Userform Combobox

[Nine Zero]

Hello All,

I have a userform with a combobox that on Initialize the combobox is loaded with about 23000+ items

I have a checkbox where it will remove all of them currently 1 by 1 and load another list of only about 200 items

When the checkbox is unchecked it will reverse it by removing the 200 1 by 1 then re-adding the original list again and vice versa

There is currently a 6 second lag to load any of the lists into the combobox which i fully understand due to the removing and re-adding

is there a "faster" way to clear a combobox other than removing 1 by 1 each item?

Thank you

 

[Nine Zero]

Ok so i just used the .Clear function and it sped it up to only about 1 second lag 1 way but the other way lags a bit more than before....weird

 

[My Aswer Is This]

Try Combobox1.clear

 

[My Aswer Is This]

Show me the script you use to load the combobox

 

[Nine Zero]

below are my 3 subs for loading and removing from the box and lastly the fourth is the checkbox

Also on Initialize ... LoadCustomers is called

Sub LoadCustomers()
Dim r As Range
Dim lastc As Integer
    With Sheets("CUSTOMER_LIST")
    lastc = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    For Each r In Sheets("CUSTOMER_LIST").Range("A2:A" & lastc)
    customernumberbox.AddItem r.Value
Next r
End Sub

Sub LoadNational()
Dim r As Range
Dim lastc As Integer
    With Sheets("NATIONAL_LIST")
    lastc = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    For Each r In Sheets("NATIONAL_LIST").Range("A1:A" & lastc)
    customernumberbox.AddItem r.Value
Next r
End Sub

Sub RemoveCustomers()
'For i = customernumberbox.ListCount - 1 To 0 Step -1
'customernumberbox.RemoveItem i
'Next i
customernumberbox.Clear
End Sub

Private Sub nationalaccount_Click()
If nationalaccount.Value = True Then
RemoveCustomers
LoadNational
Else
RemoveCustomers
LoadCustomers
End If
End Sub

 

[My Aswer Is This]

I always load comboboxes like this:

Private Sub UserForm_Initialize()
Dim lastc As Integer
lastc = Sheets("CUSTOMER_LIST").Cells(Rows.Count, "A").End(xlUp).Row
customernumberbox.List = Sheets("CUSTOMER_LIST").Range("A2:A" & lastc).Value
End Sub

 

[Nine Zero]

Whoa extremely fast!

Always thought you had to load it item by item!

Thank you very much!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Whoa extremely fast!

Always thought you had to load it item by item!

Thank you very much!