Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Excel-VBA: Form List Box Multiselect To Excel

How do I set property or what is the code I should put for getting the multiselected items from ListBox on form to range of excel cells?
I.e. I want to add list to excel after required list is selected in the ListBox.
The selection in the listbox may be, say, 1,2,3,4,5.. or at any, say, 1,2,4.. (3,5 not required) etc.
Or should I use some other controls or else?

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Multiselect Into Excel Spreadsheet
Hello All,
after many months of being VB free im trying to jump back into it, but sadly having a few problems. I would like a user to select numbers from a multiselect list box . these numbers are than matched through an sql query to hardcoded tables within that datadase and corresponding info is extracted and put into an excel spreadsheet. problem is the sql query only picks up one of the many selected numbers from the list box and drops the rest! very frustrating. im sure its a loop out of order, but cannot find where! Any help would be greatly appreciated as i have run out of ideas!

This is a combination of posted code ive picked up and lines i've added myself - heres what i have so far:

Private Sub Command1_Click()
Dim i, J, rtot, m, TL
Dim db As Database
Dim rs As DAO.Recordset
Dim ctot(1 To 4)
Dim objExcl As Excel.Application

Set db = OpenDatabase(txtDbname.Text)
Set objExcl = New Excel.Application
objExcl.Visible = True
objExcl.SheetsInNewWorkbook = 1

objExcl.ActiveSheet.Name = "Inventory Summary"

For i = 0 To List1.ListCount - 1

If List1.Selected(i) = True Then
List2.AddItem List1.List(i)'to error check

Set rs = db.OpenRecordset("select polygon_id from polygon where polygon_id = " & List1.List(i))

i = i + 1

End If
'i = i + 1
Next i

For i = 0 To rs.Fields.Count - 1
objExcl.ActiveSheet.Cells(1, i + 1).Value = rs.Fields(i).Name

J = 3

Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
objExcl.ActiveSheet.Cells(J, i + 1).Value = rs.Fields(i)
Next i

'J = J + 1


End Sub

Listbox Multiselect Drag && Drop In Excel
I've finally gotten the code down to drag an item from one list box and drop it into another listbox, but only for one item at a time. The list boxes are on a user form and I am using Excel 2002.

Can anyone help with how to select multiple items in ListBox1, drag them to and drop them into ListBox2?

Any suggestions are greatly appreciated.

It seems the DataObject used in the sample code provided by Excel Help [see code below] to carry info from one listbox to the next is limited to one item at a time. Can something else be used in its place that doesn't have this limitation?

Private Sub ListBox2_BeforeDragOver(ByVal Cancel As _
    MSForms.ReturnBoolean, ByVal Data As _
    MSForms.DataObject, ByVal X As Single, _
    ByVal Y As Single, ByVal DragState As Long, _
    ByVal Effect As MSForms.ReturnEffect, _
    ByVal Shift As Integer)
    Cancel = True
    Effect = 1
End Sub

Private Sub ListBox2_BeforeDropOrPaste(ByVal _
    Cancel As MSForms.ReturnBoolean, _
    ByVal Action As Long, ByVal Data As _
    MSForms.DataObject, ByVal X As Single, _
    ByVal Y As Single, ByVal Effect As _
    MSForms.ReturnEffect, ByVal Shift As Integer)
    Cancel = True
    Effect = 1
    ListBox2.AddItem Data.GetText
End Sub

Private Sub ListBox1_MouseMove(ByVal Button As _
     Integer, ByVal Shift As Integer, ByVal X As _
     Single, ByVal Y As Single)
    Dim MyDataObject As DataObject
    If Button = 1 Then
        Set MyDataObject = New DataObject
        Dim Effect As Integer
        MyDataObject.SetText ListBox1.Value
        Effect = MyDataObject.StartDrag
    End If
End Sub

Private Sub UserForm_Initialize()
    For i = 1 To 10
        ListBox1.AddItem "Choice " _
            & (ListBox1.ListCount + 1)
    Next i
End Sub

Edited by - doofusboy on 8/20/2007 6:16:07 AM

How To Get Selected Items From Multi-select List Box On Form In Excel.
Hello... I am having some difficulty getting the selected values from a multi-select list box in Excel. Here is my set up:

A sheet contains a button. The button envokes the form:

On this form the user selects several values in a list box and hits another button. Here is my problem. In the code behind the _click button action of the form... I am unable to retrieve the values selected by the user... I have tried serveral things I found on the net but none of them seem to work...

does anyone have any example code or ideas here?

Thanks for looking,

Loading An Existing Excel To An Excel Sheet On A Form
How can I load Sheet1 of a current Excel program to a sheet object on my VB form? I don't want to load the entire app, just one sheet.


Copy Excel Sheet To A Form's Excel Control
I have an Excel workbook. In this workbook I have a VBA form which contains an Excel Spreadsheet control. I want to copy a sheet from the actual workbook to the workbook of this control in my form. I've searched the forums and learned how to copy, but it doesn't seem to cooperate with the control. Any other methods? Thanks.

Looking For Excel OCX To Have Excel Viewed && Controled In My VB Application Form
I am looking for viewing excel application in my VB application form, is there any OCX that can help me having this functionality, I tried OLE but did not meet what Iam looking for.

I used OCX for PDF and ocx for viewing scanned document MAX and now I am looking to have Excel as well.

Appreciate your help in advance.
Many Thanks

Excel Warning - How To Avoid This Excel Form From Appearing
I have an application with an Excel Add-in that communicates with a VB Client (The client communicate with a Server -SQLdB.) This application do calculations. Sometimes when a big report is created in the server (the report is initilized from the Excel Client (Add-in)) an Excel Warning dialog pop up saying :

"Microsoft Excel is waiting for another application to complete an OLE action"

The report is only appearing after clicking on the OK button in this form. I've attached apicture of this.
Is it possible to skip this form to appear when Excel is waiting for the resulting data from the server to arrive?

Excel Issues - "Excel Cannot Determine Which Row In Your List Contains Column Labels"
Hello again,

I am working on a program that imports various Excel spreadsheets into one one workbook. The data is then sorted and calculations are made which are then summarized into one sheet. I have created a form that will enable the user to select which kind of charts they would like to generate and for what, all based on the summary of the data.

The issue I am faced with is an Excel prompt that comes up whenever I use the subtotal function for my calculations. The prompt states "Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command." I use the subtotal function on a number of different worksheets, so this prompt is coming up 5 times.

The imported data does have bold column headings as well. Is there a way to force these prompts to say OK which tells Excel to use the first line of data as my column headings without these prompts coming up? Or is there any other quick fix for this? Any advice is very much appreciated.

Show A Form On Startup In Excel(use To Be:vb And Excel)
Could someone tell me how I can set up a vb macro to run in excel when you open the spreadsheet.

I have a navigation form for the spreadsheet and I want it to be the first thing that is viewed when the user opens the spreadsheet.


Excel - Dropdown Showing List 2 Dependant On What Was Chosen In List 1
I'm programming VB in Excel. I've created a series of simple lists in a worksheet. I would like to select one object from list 1 in a cell in column A and then have the cursor cell move to the right neighbouring cell and present me with a dropdown menu showing the contents of another list dependant on what was chosen in the first cell. Is there a specific command for this?

Many thanks for any help.

Multiselect From List
I have a list box that is by populated by looping through a dataenvironment that is connected to a table called "tblRecords". Each record can have more than one Name attached to it, so I have created a separate table called "tblWO_Name". The common link between the two tables is the generic "ID" field. On a form, I am using an adodatacontrol to scroll through records, I want to have my list box highlight the multiple names that come from the tblWO_Name table. I cannot use the adodatacontrol as the datasource because then it rules out using multiselect on the list box. So far I have tried this:
Public Sub HighlightNames(ID As Long)

Dim nentrynumber As Long
Set oConnection = New ADODB.Connection
oConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\TRAMPMFG - ENGMaintenance DatabasesMean_Between_Downs.mdb;Persist Security Info=False"

Set orecordset = New ADODB.Recordset
orecordset.Open "Select * from tblWO_Name where ID = " & ID, oConnection, adOpenDynamic, adLockOptimistic


Do Until orecordset.EOF = True
nentrynumber = Form1.ListRepaired_By.ListCount
Do While nentrynumber > 0
nentrynumber = nentrynumber - 1
If Form1.ListRepaired_By.List(nentrynumber) = orecordset.Fields("RepairedBy").Value Then
Form1.ListRepaired_By.Selected(nentrynumber) = True
End If

End Sub

Then I call this subroutine from the form. This sub is scrolling through an ado connection that selects only the records whose ID matches the ID that is input onto the form (Form1).Right now, it scrolls through and temporarily highlights each name correctly, but they do not stay highlighted. When it gets to the last name in the recordset, it keeps that one highlighted. Any ideas on how to make it keep all the correct names highlighted?

Help With Multiselect And List
ok i need some help i want a command that reads the first selected and the second selected in a listbox. like say theres 1000 things in the listbox i want to be able to select 2 and display then in another listbox(list2) how would i do?

Added green "resolved" checkmark - Hack

List Box Multiselect
I have a multiselect listbox that's populated with words and each word is assigned a number through the itemdata property like this:

strSql = "SELECT * FROM Vocational ORDER BY vocdesc"

Set rstTemp = fCnn1.Execute(strSql)

Do While Not rstTemp.EOF
lstVocational.AddItem rstTemp!vocdesc
lstVocational.ItemData(lstVocational.NewIndex) = rstTemp!vocid


What I want to do is select certain indexes in the listbox based on the itemdata property without loooping through the listbox. Is that possible?

I know this is wrong but Something like this:

strSql = "SELECT internalvoc.vocid FROM internalvoc "
strSql = strSql & "WHERE referralid = " & strReferralID

Set rstTemp = fCnn1.Execute(strSql)

Do While Not rstTemp.EOF

lstVocational.Selected(lstVocational.ItemData(rstTemp!vocid)) = True


Thanks in advance,

Multiselect List Box
I have this code for my multiselect list box. If I select more than one item
the code goes through each of the items twice before it gets out.

If lstCategory.ListIndex = -1 Then Exit Sub
For intCounter = lstCategory.ListCount - 1 To 0 Step -1
If lstCategory.Selected(intCounter) = True Then
'MsgBox lstCategory.List(intCounter)
Select Case lstCategory.ListIndex
Case 0 ''Ambulance Stations
Call DrawFunction
Case 1 ''Apartments
Call DrawFunction
Case 2 ''Arenas/Stadiums
Call DrawFunction
Case 3 ''Bingo Halls
Call DrawFunction
End Select
End If
Next intCounter
End If

If my list is:
Bingo Halls

If I select
Bingo Halls
It will loop through Bingo Halls twice before it goes to Arena/Stadiums, which it will do once.
How can I stop this from happening?

Multiselect In List Box

i have a list box (multi select) i have a btn next to it and anotherlist box.
once i select item from the first list (may be more than one) and pressthe btn, the selected item goes to next list box.
now i have to unselect the first list. what should i do...

reply urgent

Thanx in advance
A. Gijesh

Multiselect List Box
Hey guys,

I have a listbox with multiselect = True. If I have several values selected in the list box
how do I iterate through them to perform a function with the values selected?

I can get one value but not all of them.

Here is what I was thinking:

For I = 0 To lstNeedPublish.ListCount - 1
    If lstNeedPublish.Selected(I) = True Then
    End If

Thanks for your help!!!


Software Developer by day - MAN OF GOD all the time.

List Box - Multiselect
I have a bounded listbox on my form. the probelm is that on design tme i set the multiselect option to 2 that is extended but when I run the form the listbox's multiselect property changes to 0 that is none. Can anyone tell me why this happens? PLs help

Lloyd Dsouza

Getting Values From Multiselect List Box
How can I get the items that I select in a multiselect list box and put the selected values(text) into an array?

All help would be appreciated.

MultiSelect From A Drop-Down List
Hello all,

Can I MultiSelect from a Drop-Down List? From what I find, I can only MultiSelect from a List Box. The Drop-Down List is generated in the Combo Box. Please help!

Multiselect List Boxes
Still can't get this to work. please help
Open FileName For Random As #1 Len = Len(P)
For counter = 1 To lstemployeeonproject.ListIndex(Selected)
If lstemployeeonproject.Selected(counter) = True Then
P.projectemployees = lstemployeeonproject.Selected(counter)
Put #1, numberofrecords + 1, P
End If
Next counter
Close #1
numberofrecords = numberofrecords + 1

Multiselect List Box With A Date Range
I attach an MDB that the has the multiselect listbox
control. I would however, like to include on the same form
a criteria for a date range; start date, end date and then
build that into the sql when I run the query.
I woiuld also like to make the "All" stay at the top of the
list box.

Can anyone please help me on these 2 items.

Many thanks

CommonDialog1.ShowOpen Multiselect + Detail List
By default the CommonDialog ShowOpen is set to single select and arranges the listing accross the dialog view area.

I want it to be multiselect and the list of files to be in a single column.

How can I do that?

*** Resolved *** LISTBOX: How To Set Listindex=0 In A Multiselect List?

I need to set the listindex = 0 in a multi-selection Listbox. How to do that?


Thanks cafeenman

Multiselect In Common Dialog Open (.FileName Not Returning List)
I am using a common dialog open control to open multiple files. Here is my code.

VB Code:
'On Error GoTo EndIt    CD1.Flags = cdlOFNAllowMultiselect + cdlOFNExplorer    CD1.InitDir = App.Path    CD1.Filter = "Text Files (*.txt)|*.txt|All Files (*.*)|*.*"    'CD1.MaxFileSize = 32767    CD1.ShowOpen        MsgBox CD1.FileName

When I select 1 file I get a msgbox with it's full path, but if I select multiple files all that is in the msgbox is the path to the folder of the selections (and not the file names).

If I remove the cdlOFNExplorer flag then I get the ugly Win95 dialog, but I get the proper value for CD1.Filename.

Any ideas?

VB To Excel (list Box)
oSheet.Cells(z, 1).Value = Form1.List1.List(z)
oSheet.Cells(y, 2).Value = Form1.List2.List(y)

How do i get it to loop down...if i have x amount of things in a list box...
But it wont list them all with a z - 1 function or anything...

I woul dhave to do x amount of osheet.cells = etc.

Anyone have a tip for me?


Excel List
Where can i find a EXCEL discussion list? somthing like VB-WORLD?

How Do I Use A Drive List Box In Excel?
I am trying to add a drive list box into an Excel macro.

What I want to do is to click a command button on the spreadsheet and have a drive list box open to allow me to chooses and load a file into an excel spreadsheet. From this spreadsheet I then want to block-copy all the data back into the one with the macro.

Is this possible with VBA? I can't figure it out and the help files are worse than useless.


PS any idea why I can't use my avatar on this forum? It works on others.

List Filenames In A Dir From Within Excel

Using Excel, I'm trying to list all filenames within a given directory.

Can anyone help?


Problems To Get A List In Excel
Hi there i want to get a list in VB to get in excel. Like the first item on cell 2,2 en the second on cell 2,3 enz. What's wrong?

Dim objExcel As Object
Dim objBook As Object
Dim objSheet As Object

Set objExcel = CreateObject("Excel.Application")
Set objBook = & "17.xls")
Set objSheet = objBook.Worksheets("inschrijving")
objExcel.Visible = True

With objExcel.activesheet
.cells(2, 2).Value = List1.Text
.cells(3, 2).Value = List1.Text
.cells(4, 2).Value = List1.Text

End With

End Sub

Drive List Box In Excel??
In excel is there a control for a drivelist and folderlist etc... If so what control is it?

Excel Arguement List

When i write a function in VB like:

Private Function OOO(mystringshouldbehere as string, mynumberhere as long)

end function

Then when i inside VB write "OOO(" Then it says to me what the names of the arguements are and what type they should be.

The problem is when calling a function made in VBA from excel document: "=OOO(" it doesn't show any help.

Is there anyway to make that appear?

Excel VB List And Filter
I would like to create a list of data, similar to what would be in rows and columns of a table in excel - but purely in VBA. I assume there is a way to do this in an array or something? Hope someone can help.

What I would like to do is create this database and then be able to filter a specific column of information and then display the remaining items and their corresponding adjacent columns of data. All this using only VBA, no excel spreadsheets.

Can someone post code for this as an example, this is how I learn best.

Combo List In Excel Using VB
I am having data in a Flex grid
I transfered those data to Excel at run time

But i need to have one combo box in excel

How can i create combo box in excel in run time

Please reply

Thank you

Excel List Box Help Please (RESOLVED)
hello all. I use the following code to add to a multi column list box but how do I get the information in to seperate txt boxes.

VB Code:
Private Sub lststationery_Click()txtdes.Text = lststationery.Valuetxtcode.Text ' how do I get the second and third columns of data pleasetxtprice.TextEnd Sub Private Sub UserForm_Initialize()For n = 2 To 59 'I always use n cos on the ZX Spectrum, next n could be typed by pressing n twice.  It's my convention, most people use counter or suchlike    Set curcell = Worksheets(2).Cells(n, 10)    EqDesc(n) = curcell.Value    Set curcell = Worksheets(2).Cells(n, 11)    EqCode(n) = curcell.Value    Set curcell = Worksheets(2).Cells(n, 12)    EqPrice(n) = curcell.Value    With lststationery    .AddItem (EqDesc(n)) 'column 1    .List(.ListCount - 1, 1) = EqCode(n) 'column 2    .List(.ListCount - 1, 2) = EqPrice(n) 'column 3    End WithNext n End Sub

help me im stumped.

List Indexing (VB In Excel)
Hi I am populating a text box with values from a number of excel cells, however the number of cells that contain data is not always the same i.e. I may have 10 values in one row and 12 in another, up to say a maximum of 50, so in order to avoid adding each cell one at a time, I am wondering if I could put a range in e.g. column 2 row 2 to column 51 row 2. Please see attachment. in my current code below I am using & to add all the row and column data together, I would like to avoid this approach.

Private Sub ComboBox1_Change()
With Me.ComboBox1
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("DATA LOADER.xls", _False, True)
Header.Value = SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 2).Value & " " _
& SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 3) & " " _
& SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 4) & " " _
& SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 5)
End With
End Sub

Excel: Sorted List
Hi all,
I try to find if a list in a range of cells is sorted. For example:
I have datalist in range(A3:F21) and I want to see if this list is sorted by column A (A3:A21) or not.
I appreciate if you can help on this
Thanks in advance

List Of Constants In Excel
Does anyone know where I can find the constants, (think that's right?), for excel and vba?

For example:

I use php and com to run excel. I want to do a graph, no problem, but to do formatting of the graph, I need to know the number value of certain attributes because the text way doesn't work.

If I want to do a 3d pie chart.

ChartType = xl3dPie does not work but ChartType=-4102 does.

Where do I find a listing of these values for this and other attributes such as:

Legend.Position="xlBottom" etc...


Getting The List Of Files From A Dir In Excel
I want to get the list of files in a single directory so I can then open them one after the other. How do I get the list without using the Microsoft directory and fileList controls (since I have no form)? I want to load an array with the names then I'll cycle through that to open each file.

Thanks a lot everyone.

The worst thing about wearing glasses is them being on your face.
Nothing is impossible, it just looks that way.

Help: I Need To Print A List (in Excel)
Hi !

Could somebody help me to print a ListBox ?

I´m displaying a UserForm that contains a ListBox with non constant data; i.e. it depends on the user the number of
rows displaying on the List; so my problem is, if I use the PrintForm function it prints a part of the List and if the List it´s
too long (scrollbars appear ) then last part of the list doesn´t prints.

I hope you can help me.

Thanks !

Can Excel Populate A List Via VBA?
Hi, I'm good with VB.NET but not very well versed in VBA. I have an excel 2007 spreadsheet which has a list in one of it's cols. As the contents of items in the list will change from time to time, is there a way I can simply have VBA read a simple text file and populate the list? Or do the items I want in the list need to be pasted in a sheet and then set manually?

Thank you. Gordon

List Of Excel Commands On COM
How can I get a list of all available commands for automating Excel thru VB...
For Example, oExcel in the following...
oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False
oExcel.visible = True

Two calls for oExcel are DisplayAlerts and visible.
Where can I find a list of other calls

Thanks in advance!

Need Help With Printing Address List In Excel
I have this code which prints 8 lines of address from a worksheet.
What I need help with is to leave 3 blank lines after each address.
Any help is much appreciated, thank you all!

Dim TheRow As Long, TheSource As Long
TheRow = 2
TheSource = 3

Ref = MsgBox("Send filtered results to printing format now?", vbOKCancel)

If Ref = vbCancel Then
Exit Sub

For j = 1 To 8
For k = 1 To 3
If Not Sheets("Contacts").Rows(TheSource).Hidden Then
Range(Cells(TheSource, 9), Cells(TheSource, 16)).Copy
With Sheets("Print Addresses")
.Range(.Cells(TheRow, k).Address & ":" & Cells(TheRow, k).Address).PasteSpecial Transpose:=True
End With
k = k - 1
End If
TheSource = TheSource + 1
Next k
TheRow = TheRow + 8
Next j
Loop Until Cells(TheSource, 1).Value = ""
End If

Need To Show List Of EXCEL Macros In VB6
I have a VB6 application which interacts with EXCEL spreadsheets. The spreadsheets (actuually a PERSONAL.XLS spreadsheet which stores many macros) have macros (VBA) and I would like to be able to present my user with a list of the available macros where they can choose the one they want to run.

I cannot seem to find any way to get the list of macros associated with a spreadsheet.

Any sugestions?


Excel Sheets And List Boxes
Hi, I have some code that finds all available sheets in a workbook and puts them all into a list box. How do I make it so that the sheet that's highlighted in the listbox is the one that VB will write data to? thanks for any help you can give me. Heres my code for getting the sheets if you need it.

Dim SheetName As String
Dim Sheetcount As String
Dim i As Integer

With xlsApp.ActiveWorkbook

Sheetcount = .Worksheets.Count

For i = 1 To Sheetcount
SheetName = .Worksheets(i).Name
lstSheets.AddItem SheetName
Next i
End With

Excel VBA Sort An Array List
Im not even sure where to start with this coding, perhaps someone can post me a sample code for an example. Please note, I have never worked with array's before, so feel free to speak to me in n00b language.

The facts:
I have 8 text boxes on a form (in Excel VBA) each contains a time in format hh:mm AM/PM. Not all of these times will be in order, so textbox1 might be 08:00 AM, textbox2 10:00 AM, textbox3 09:00 AM... and so on.

What I need:
I need excel to sort these for me... so the array would pull from the textboxes and list first textbox1, then textbox3, then textbox2... and so on.

I assume I also need to know how to create the array, using Dim? and how to pull each value from the array to use in a formula in another procedure?


- Desperate!

Data Transfer From A List Box To Excel
Hi. I was wondering if anyone could help me with something which is probably quite simple but I can't seem to find on MSDN.

I am basically wanting to transfer data from a VB list box to a column in an Excel worksheet.

Thanks for any help you can give.

Send List In Listview To Excel
I would like to know, how to sent the list show in the listview to excel?Have a sample for starter

Creating List Of Values In Excel From VB
i have to create a execel sheet from Vb

I have created the code.
But i have to add a list to values to a column Say A, so that the user can any one in the list of values.
After a value is selected in the column the next column say B should have a value based on Value in A



When Yes is selected in Column A then the value in Column B should be Y
When No is selected in Column A then the value in Column B should be N

Please help me in the same

Thanks in Advance

List All Externaldata Objects Excel
Is there a way to list all externaldata object (Externaldata_???) in a Excel workbook?

Copyright 2005-08, All rights reserved