Dynamically populate listbox in userform from array?

[TheWennerWoman]

Hello,

I have the following

Dim rRangeCheck As Range
On Error Resume Next
Set rRangeCheck = Range("Postcode_List")
On Error GoTo 0
If rRangeCheck Is Nothing Then
Exit Sub
ElseIf Range("Postcode_List").Count <= 1 Then
Exit Sub
Else

Dim NameList, Entry
ListBox1.Clear
NameList = Range("Postcode_List").Value

With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each Entry In NameList
        If InStr(1, Entry, TextBox1.Value, 1) > 0 Then .Item(Entry) = Entry
    Next
    If .Count > 0 Then ListBox1.List = .keys
End With
   
   
End If

This has about 400 postcodes (zip codes to our friends across the pond) in a sheet and as the user starts to type into the listbox then the list narrows down (a bit like when you start typing into Google, it's kind a pseudo intelligent approach). It works perfectly.

I've now been asked to enhance this to include every single postcode in the UK - there are 1.7 million of them

I can't put them in a worksheet (too many rows) so I've put them in an array (1.7million rows, 1 column). Array is called myArray.

Can I adapt what I've got to do exactly the same but use the array as the source? I was hoping, to speed things up, to only start intelligently looking after the user has typed in maybe four characters (otherwise it'll just be too slow). Or maybe link to an Access table?

Any thoughts always greatly appreciated.

Thank you for reading.

 

[RoryA]

If you load the data into a string array, you can use the Filter function which does what your current code is doing.

 

[Akuini]

I can't put them in a worksheet (too many rows) so I've put them in an array (1.7million rows, 1 column). Array is called myArray.

How did you put them in the array?

 

[TheWennerWoman]

How did you put them in the array?

Extracted them from a SQL table

 

[Akuini]

Basically this is how I would do it:
1. Load the array in userform Initialize event.
2. Use Textbox Change event to search the list by typing some characters.
3. I can type multiple keywords separated by a space.
4. Limit how many items displayed in the Listbox, say only up to 1000 items, to make it faster.

Condition: Data are all uppercase.
Note:
Label1 shows how many items that are found & displayed.
Label2 shows how long it take to search the list every time the textbox changes. You probably don't need it, it's just for speed test.

Change this part to suit:

Private Const LN As Long = 1000  'maximum number of rows displayed in the listbox
Private Const CN As Long = 3  'start seaching after typing certain number of character

Private Sub UserForm_Initialize()
    'load data to myArray
    myArray = Sheets("Sheet1").ListObjects("Table1").DataBodyRange.Value

The workbook:

TheWennerWoman - userform - searchable, 1 column listbox, 1 textbox

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[TheWennerWoman]

Basically this is how I would do it:
1. Load the array in userform Initialize event.
2. Use Textbox Change event to search the list by typing some characters.
3. I can type multiple keywords separated by a space.
4. Limit how many items displayed in the Listbox, say only up to 1000 items, to make it faster.

Condition: Data are all uppercase.
Note:
Label1 shows how many items that are found & displayed.
Label2 shows how long it take to search the list every time the textbox changes. You probably don't need it, it's just for speed test.

Change this part to suit:

Private Const LN As Long = 1000  'maximum number of rows displayed in the listbox
Private Const CN As Long = 3  'start seaching after typing certain number of character

Private Sub UserForm_Initialize()
    'load data to myArray
    myArray = Sheets("Sheet1").ListObjects("Table1").DataBodyRange.Value

The workbook:

TheWennerWoman - userform - searchable, 1 column listbox, 1 textbox

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Very good, works a treat, many thanks

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.