PLZ / Ort Auswahl
12.08.2004 19:57:37
ChrisL
Hi Birkel (-Nudel)
Die Datei https://www.herber.de/bbs/user/9570.xls wurde aus Datenschutzgründen gelöscht
Viel Spass
Chris
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Dim Anzahl As Byte
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
Select Case WorksheetFunction.CountIf(WS2.Columns(1), Target)
Case 0: Exit Sub
Case 1
WS1.Range("B2") = WS2.Cells(WorksheetFunction.Match(Target, WS2.Columns(1), 0), 2)
Case Else
Load UserForm1
UserForm1.Show
End Select
End If
End Sub
Private Sub CommandButton1_Click()
Worksheets("Tabelle1").Range("B2") = ListBox1
Unload UserForm1
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub
Private Sub ListBox1_Click()
CommandButton1.Enabled = True
End Sub
Private Sub UserForm_Initialize()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iZeile As Long
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
iZeile = WorksheetFunction.Match(WS1.Range("A2"), WS2.Columns(1), 0)
Do Until WS2.Cells(iZeile, 1) <> WS1.Range("A2")
ListBox1.AddItem WS2.Cells(iZeile, 2)
iZeile = iZeile + 1
Loop
End Sub