Thema: Selektieren von über ComboBox-Felder ausgewählten Werten

ClassModule: Tabelle2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   If Target.Column <= 4 Then Call SetCbo
End Sub

StandardModule: basMain

Sub SetCbo()
   Dim arr() As Variant
   Dim iRow As Integer, iRowL As Integer
   Dim iCol As Integer, iArr As Integer
   iCol = ActiveCell.Column
   If iCol > 4 Then Exit Sub
   iRowL = Cells(Rows.Count, 1).End(xlUp).Row
   For iRow = 2 To iRowL
      If Not IsEmpty(Cells(iRow, iCol)) Then
         iArr = iArr + 1
         ReDim Preserve arr(1 To iArr)
         arr(iArr) = Cells(iRow, iCol).Value
      End If
   Next iRow
   If iArr > 1 Then QuickSort arr
   With ActiveSheet.DropDowns(iCol)
      .RemoveAllItems
      .AddItem Cells(1, iCol)
      For iArr = 1 To UBound(arr)
         .AddItem arr(iArr)
      Next iArr
   End With
End Sub

Sub Auswahl()
   Dim drpDwn As DropDown
   Dim iRow As Integer, iCol As Integer
   iCol = Right(Application.Caller, 1)
   Set drpDwn = ActiveSheet.DropDowns(iCol)
   If drpDwn.ListIndex = 1 Then Exit Sub
   iRow = WorksheetFunction.Match _
      (CInt(drpDwn.List(drpDwn.ListIndex)), Columns(iCol), 0)
   Cells(iRow, iCol).Select
   drpDwn.ListIndex = 1
End Sub

Private Sub QuickSort(ByRef VA_array, Optional V_Low1, Optional V_high1)
   On Error Resume Next
   Dim V_Low2, V_high2, V_loop As Integer
   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_high2)
      While (VA_array(V_Low2) < V_val1 And _
            V_Low2 < V_high1)
         V_Low2 = V_Low2 + 1
      Wend
      While (VA_array(V_high2) > V_val1 And _
            V_high2 > V_Low1)
         V_high2 = V_high2 - 1
      Wend
      If (V_Low2 <= V_high2) Then
         V_val2 = VA_array(V_Low2)
         VA_array(V_Low2) = VA_array(V_high2)
         VA_array(V_high2) = V_val2
         V_Low2 = V_Low2 + 1
         V_high2 = V_high2 - 1
      End If
   Wend
   If (V_high2 > V_Low1) Then Call _
      QuickSort(VA_array, V_Low1, V_high2)
      If (V_Low2 < V_high1) Then Call _
         QuickSort(VA_array, V_Low2, V_high1)
End Sub

Beiträge aus dem Excel-Forum zu den Themen Dialog und ComboBox