doppelte Werte in ComboBox
05.10.2018 10:48:59
Tim
Private Sub UserForm_Initialize()
'ComboBoxes füllen ohne doppelte Einträge
'Annahme: alle Spalten haben die gleiche Anzahl von Einträgen
'Annahme: die Werte aus Spalte A, B und F sollen in ComboBox1, 2, und 3 eingelesen werden:
Dim hsh1 As Object, hsh2 As Object, hsh3 As Object
Dim i As Long, lngLR As Long
Set hsh1 = CreateObject("Scripting.Dictionary")
Set hsh2 = CreateObject("Scripting.Dictionary")
Set hsh3 = CreateObject("Scripting.Dictionary")
With Sheets("ListBox")
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row 'letzte Zeile in Spalte A
hsh1(.Cells(i, 1).Text) = 0 '1 = Spalte A
hsh2(.Cells(i, 2).Text) = 0 '2 = Spalte B
hsh3(.Cells(i, 6).Text) = 0 '6 = Spalte F
Next
End With
Me.ComboBox1.List = Application.Transpose(hsh1.Keys)
Me.ComboBox2.List = Application.Transpose(hsh2.Keys)
Me.ComboBox3.List = Application.Transpose(hsh3.Keys)
Set hsh1 = Nothing
Set hsh2 = Nothing
Set hsh3 = Nothing
End Sub
Anzeige