Drop Down list control using VBA

gtrp

New Member
Joined
Jul 15, 2004
Messages
40
Hi

I want to be able to populate a drop down list with its parameters using VBA (from a pre-existing array of values) and then use VBA to read the final selection from the list as made by the user.

Any advice/coding help that allows to do that?

Regards,

G
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
This is basic coding for a listbox (or combo) in a userform. If you use one that refers to a worksheet range you only have to change the contents of that range.

Code:
'-------------------------------------------
'- initialise form
Private Sub UserForm_Initialize()
    Set DataSheet = ThisWorkbook.Worksheets("data")
    Set MyList = DataSheet.Range("FileList")
    Rw = 1
    FileListBox.Clear
    While MyList.Cells(Rw, 1).Value <> ""
        FileListBox.AddItem
        FileListBox.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
        FileListBox.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value
        Rw = Rw + 1
    Wend
End Sub
'----------------------------------
 
Upvote 0
Thanks!

The thing is that I do not want to use a range in excel to populate the drop down list. I am reading a list of countries from an access database and want the drop down list to be populated with the countries without copying the countries list into an excel range. The countries list is stored in an array variable in a VBA module.

Regards

G
 
Upvote 0
So something like :-

Code:
For rw = 1 To 10
    FileListBox.AddItem 
    FileListBox.List(rw - 1, 0) = MyArray(rw)
Next
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top