Ich habe eine Adressenliste (Name, Str, Ort, Tel, Fax, etc.)
und möchte in einer ComboBox nur die Namen anzeigen, bei denen die FaxNr. eingetragen ist.
Kann mir jemand eine Tip geben?
Danke
Herbert
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Or Target.Column = 5 Then
Dim iZeile As Long
ComboBox1.Clear
For iZeile = 2 To Range("E65536").End(xlUp).Row
If Cells(iZeile, 5) <> "" Then ComboBox1.AddItem Cells(iZeile, 1)
Next iZeile
End If
End Sub
Private Sub Workbook_Open()
Dim WB1 As Workbook, WB2 As Workbook
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iZeile As Long
Set WB1 = Workbooks.Open("C:\Test\Adr.xls")
Set WS1 = WB1.Worksheets("Tabelle1")
Set WB2 = ThisWorkbook
Set WS2 = WB2.Worksheets("Tabelle1")
'ActiveWindow.Visible = False
With Worksheets("Tabelle1").ComboBox1
.Clear
For iZeile = 2 To WS1.Range("E65536").End(xlUp).Row
If WS1.Cells(iZeile, 5) <> "" Then .AddItem WS1.Cells(iZeile, 1)
Next iZeile
WB1.Close
End With
End Sub