AW: ComboBox1.AddItem automatisch
12.09.2008 10:43:00
Rudi
Hallo,
die Bemerkungen in Tab1!A:A
in der UF:
Private Sub ComboBox1_DropButtonClick()
Dim colTmp As New Collection, arrList(), n As Integer
Dim iRow As Long
ReDim arrList(1 To 20)
With Sheets(1)
For iRow = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Err.Clear
On Error Resume Next
colTmp.Add .Cells(iRow, 1), .Cells(iRow, 1)
If Err = 0 Then
n = n + 1
arrList(n) = Cells(iRow, 1)
End If
If n = 20 Then Exit For
Next
End With
ReDim Preserve arrList(1 To n)
QuickSort arrList
ComboBox1.List = arrList
End Sub
in ein Modul:
Sub QuickSort(ByRef VA_Array, Optional V_Low1, Optional V_High1)
On Error Resume Next
Dim V_Low2 As Long, V_High2 As Long
Dim V_Val1, V_Val2 As Variant
If IsMissing(V_Low1) Then
V_Low1 = LBound(VA_Array, 1)
End If
If IsMissing(V_High1) Then
V_High1 = UBound(VA_Array, 1)
End If
V_Low2 = V_Low1
V_High2 = V_High1
V_Val1 = VA_Array((V_Low1 + V_High1) / 2)
While (V_Low2 V_Val1 And _
V_High2 > V_Low1)
V_High2 = V_High2 - 1
Wend
If (V_Low2 V_Low1) Then Call _
QuickSort(VA_Array, V_Low1, V_High2)
If (V_Low2
Gruß
Rudi