PDA

View Full Version : Filling ComboBox?



GhostofDoom
12-20-2019, 03:28 AM
Hello,

is there a way so it can be faster loading and adding 2 values in 1 combo

i use this code
but takes ages
and i can't make the last range "B to count instead of 100000
and can't start at A2 either :(





Sub FillCustomers()
ComboBox1.Clear

Dim sSheet As Worksheet, rSource As String
Dim oDictionary As Object
Set sSheet = Sheets("ListCustomers")
Set oDictionary = CreateObject("Scripting.Dictionary") 'get duplicates

For i = 1 To sSheet.Range("A100000:B100000" & Lastrow).End(xlUp).Row
If sSheet.Range("A" & i).Text = "firstname" Then
ElseIf sSheet.Range("B" & i).Text = "name" Then
ElseIf sSheet.Range("A" & i).Text = "" Then
ElseIf sSheet.Range("B" & i).Text = "" Then
Else
If oDictionary.Exists(sSheet.Range("A" & i).Text & " " & sSheet.Range("B" & i).Text) Then

Else
oDictionary.Add sSheet.Range("A" & i).Text & " " & sSheet.Range("B" & i).Text, 0
ComboBox1.AddItem (sSheet.Range("A" & i).Text & " " & sSheet.Range("B" & i).Text)
End If

End If
Next i
Exit Sub

End Sub


Thank you

snb
12-20-2019, 03:38 AM
See: http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

GhostofDoom
12-20-2019, 03:59 AM
Hi snb,

yeah i found this page also
but not the way i was looking for
well.. i don't see anything
but offcource i could be wrong.


edit:

found something
but how can i add my second column?



sn = Sheets("ListCustomers").Range("A2:B100")
With CreateObject("scripting.dictionary")
For Each cl In sn
If cl <> "" And Not .exists(cl) Then .Add cl, Nothing
Next

ComboBox1.List = .keys




so i will get Firstname Name in the combox

snb
12-20-2019, 04:36 AM
Sub M_snb()
sn= Sheets("ListCustomers").cells(1).currentregion

with CreateObject("Scripting.Dictionary")
for j=1 to ubound(sn)
.item(sn(j,1)&sn(j,2))=array(sn(j,1),sn(j,2))
next

ComboBox1.List = application.index(.items,0,0)
end with
End Sub

See http://www.snb-vba.eu/VBA_Dictionary_en.html
(http://www.snb-vba.eu/VBA_Dictionary_en.html)
NB. Use Arrays instead of writing/reading umpth times in/from the worksheet.
Use builtin properties like usedrange, currentregion, etc.

GhostofDoom
12-20-2019, 04:50 AM
thanks snb,

how can i ignore the header? so it won't be in the combobox

Thanks

edit:

nvm got it
just changed the j=1 to j=2

thanks alot snb