AW: Combobox Userform: Dictionary
11.12.2019 15:46:11
Nepumuk
Hallo Albert,
wozu löschen? Lies einfach die ersten zwei Zeilen nicht ein.
Sortieren geht so:
Option Explicit
Private Sub UserForm_Initialize()
Dim avntValues As Variant, vntItem As Variant
Dim objDictionary As Object
With Worksheets("Stückliste")
avntValues = .Range(.Cells(3, 3), .Cells(.Rows.Count, 3).End(xlUp)).Value2
End With
Set objDictionary = CreateObject(Class:="Scripting.Dictionary")
For Each vntItem In avntValues
objDictionary.Item(Key:=vntItem) = vbNullString
Next
ComboBox1.List = objDictionary.Keys
Call QickSort(0, ComboBox1.ListCount - 1)
ComboBox2.List = ComboBox1.List
Set objDictionary = Nothing
End Sub
Private Sub QickSort(ByVal pvlngLBorder As Long, ByVal pvlngUBorder As Long)
Dim ialngIndex1 As Long, ialngIndex2 As Long
Dim strBuffer As String, strTemp As String
ialngIndex1 = pvlngLBorder
ialngIndex2 = pvlngUBorder
With ComboBox1
strTemp = .List((ialngIndex1 + ialngIndex2) \ 2)
Do
Do While .List(ialngIndex1) < strTemp
ialngIndex1 = ialngIndex1 + 1
Loop
Do While strTemp < .List(ialngIndex2)
ialngIndex2 = ialngIndex2 - 1
Loop
If ialngIndex1 <= ialngIndex2 Then
strBuffer = .List(ialngIndex1)
.List(ialngIndex1) = _
.List(ialngIndex2, 0)
.List(ialngIndex2) = strBuffer
ialngIndex1 = ialngIndex1 + 1
ialngIndex2 = ialngIndex2 - 1
End If
Loop Until ialngIndex1 > ialngIndex2
End With
If pvlngLBorder < ialngIndex2 Then Call QickSort(pvlngLBorder, ialngIndex2)
If ialngIndex1 < pvlngUBorder Then Call QickSort(ialngIndex1, pvlngUBorder)
End Sub
Gruß
Nepumuk