[Closed] VBA userforms - dropdown list for 500 items

I normally add a second text box above the list and stick some code in the onchange event for it to then limit the list

so, selecting one user from a whole bunch this lets me limit the list.

sWho = Trim(Nz(Me.txtWhichUser.Text, "")) 'txtWhichUser is where they type part of the name
sSQL = "SELECT * FROM AllUsers "
If sWho <> "" Then
sSQL =  sSQL & " WHERE name LIKE ""*" & sWho & "*"""

end if

lstUsers.rowsource = sSQL

then requery the list

then i double click to select the people and add them to whatever I'm adding them to.  Other methods are available, thats just what I tend to do

@leffeboy

Thanks I'll give your suggestion a go.

(Forgot to mention I don't seem to have ActiveX enabled in this userform in case that affects any other solutions).

I'm working directly in MS Access and I haven't used straight VBA forms for a while so I can't remember exactly how it works there.  I just wanted to give you the idea.

Be a bit more complex in Excel VBA, you'd have to use a listbox and populate the list line by line eg

Me.listFilter.Clear

For N = 1 to 500

Item=Array(N)
Me.listFilter.AddItem Item

Next N

That would shove 500 items from an array into a list box.

If you enable the selection tick box for each row, you can then scan the list to see what they selected eg

For N = 0 To Me.listFilter.ListCount - 1

if Me.listFilter.Selected(N) = True then
'They selected this item, so do something with it...
End If

Next N

To make it auto filter the list you'd have to detect a change in a txt box using the built in event and then re-populate the form with a filtered sub set of the 500 items.

That's how I did this form

[url= https://live.staticflickr.com/65535/50404457666_b83a1aca09.jp g" target="_blank">https://live.staticflickr.com/65535/50404457666_b83a1aca09.jp g"/> [/img][/url][url= https://flic.kr/p/2jN52cy ]Listbox form[/url] by [url= https://www.flickr.com/photos/brf/ ]Ben Freeman[/url], on Flickr

Thanks. Would that mean manually entering the 500 items line by line in the code?

Thanks. Would that mean manually entering the 500 items line by line in the code?

Only if you're a masochist!

I'd either read the list from a worksheet, which could be hidden, or read in from a text file.

Reading from a worksheet is very simple, so I'd start with that. Files are a bit more tricky as you need to make sure every user has it and in the right place and its not been corrupted etc.

Phew! Thanks again. I've got an Excel file with the 500 so I'll try and use that.

Thanks again for all replies.

Here you go - simple Excel Workbook with a list box form which sucks in 500 items from a Worksheet

To run, load in Excel, hit Alt-F11 (to launch VBA IDE), then Ctrl-G (to launch Immediate window) then type

call ExampleForm