AW: ComboBox sortieren
19.05.2016 16:59:46
Nepumuk
Hallo,
so:
Option Explicit
Const C_mstrDatenblatt As String = "Database"
Const C_mstrZielblatt As String = "Dashboard"
Dim mlngLast As Long
Private Sub UserForm_Activate()
Dim mobjDic As Object
Dim mlngZ As Long
'Erste Combobox. Jeder Standort aus Spalte A wird nur einmal angezeigt.
Set mobjDic = CreateObject("Scripting.Dictionary")
With Worksheets(C_mstrDatenblatt)
For mlngZ = 5 To mlngLast
If Not IsEmpty(.Cells(mlngZ, 1).Value) Then _
mobjDic(.Cells(mlngZ, 1).Value) = 0
Next
End With
ComboBox1.List = mobjDic.keys
Call QickSort(0, ComboBox1.ListCount - 1, ComboBox1)
Call mobjDic.RemoveAll
With Worksheets(C_mstrDatenblatt)
For mlngZ = 5 To mlngLast
If .Cells(mlngZ, 1).Value = Me.ComboBox1.Value Then _
mobjDic(.Cells(mlngZ, 2).Value) = 0
Next
End With
ComboBox2.List = mobjDic.keys
Call QickSort(0, ComboBox2.ListCount - 1, ComboBox2)
Set mobjDic = Nothing
End Sub
Private Sub QickSort(ByVal pvlngLBorder1 As Long, _
ByVal pvlngUBorder1 As Long, ByRef probjCombobox As MSForms.ComboBox)
Dim ialngIndex1 As Long, ialngIndex2 As Long
Dim strBuffer1 As String, strTemp1 As String
ialngIndex1 = pvlngLBorder1
ialngIndex2 = pvlngUBorder1
With probjCombobox
strTemp1 = .List((ialngIndex1 + ialngIndex2) \ 2, 0)
Do
Do While .List(ialngIndex1, 0) < strTemp1
ialngIndex1 = ialngIndex1 + 1
Loop
Do While strTemp1 < .List(ialngIndex2, 0)
ialngIndex2 = ialngIndex2 - 1
Loop
If ialngIndex1 <= ialngIndex2 Then
strBuffer1 = .List(ialngIndex1, 0)
.List(ialngIndex1, 0) = _
.List(ialngIndex2, 0)
.List(ialngIndex2, 0) = strBuffer1
ialngIndex1 = ialngIndex1 + 1
ialngIndex2 = ialngIndex2 - 1
End If
Loop Until ialngIndex1 > ialngIndex2
End With
If pvlngLBorder1 < ialngIndex2 Then Call QickSort(pvlngLBorder1, ialngIndex2, probjCombobox)
If ialngIndex1 < pvlngUBorder1 Then Call QickSort(ialngIndex1, pvlngUBorder1, probjCombobox)
End Sub
Private Sub CommandButton1_Click()
'Bestätigt die ausgewählten Combobox Werte, schließt das UserForm1 und öffnet das UserForm2.
Dim iZeile As Long
If ComboBox1.ListIndex >= 0 And ComboBox2.ListIndex >= 0 Then
With Worksheets(C_mstrDatenblatt)
For iZeile = 5 To .Cells(.Rows.Count, 1).End(xlUp).Row
If .Cells(iZeile, 1) = ComboBox1 And .Cells(iZeile, 2) = ComboBox2 Then
zeile = iZeile
Exit For
End If
Next iZeile
End With
Unload Me
UserForm2.Show
End If
End Sub
Private Sub CommandButton3_Click()
'UserForm2 öffnen.
Unload Me
UserForm3.Show
End Sub
Private Sub UserForm1_Schließen_Click()
'UserForm1 schließen.
Unload Me
End Sub
Private Sub UserForm_Initialize()
'Bei Start des Userform1 wird die unterste Zeile in Spalte A ermittelt.
mlngLast = Worksheets(C_mstrDatenblatt).Cells(Rows.Count, 1).End(xlUp).Row
End Sub
Gruß
Nepumuk