Danke
Private Sub CommandButton1_Click()
Fill_ComboBox WorkSheetName:="Tabelle1", _
SearchRangeRow:="A", _
ComboBox:=ComboBox1
End Sub
Private Sub Fill_ComboBox(WorkSheetName As String, SearchRangeRow As String, ComboBox As Control)
Dim CurrentWorksheet As Worksheet
Dim CurrentRange As Range
Dim RangeValue As Variant
Dim LastValue As Variant
Dim k As Long
'Lösche die Einträge in der Combobox-Liste !
ComboBox1.Clear
'nun arbeite mit einem beliebigen Worksheet
Set CurrentWorksheet = Application.Worksheets.Item(WorkSheetName)
If Not CurrentWorksheet Is Nothing Then
'Welcher Range
Set CurrentRange = CurrentWorksheet.Range(SearchRangeRow & "1")
If Not CurrentRange Is Nothing Then
k = 0
RangeValue = CurrentRange.Offset(k, 0).Value
LastValue = ""
While CBool(Len(RangeValue))
RangeValue = CurrentRange.Offset(k, 0).Value
'da Strings bzw. Zeichenketten..
If Not InStr(LastValue, RangeValue) = 1 Then
LastValue = RangeValue
ComboBox.AddItem RangeValue
End If
k = k + 1
Wend
Set CurrentRange = Nothing
End If
Set CurrentWorksheet = Nothing
End If
End Sub