kann mir eventuell zu diesem Code hier einen Tip geben, was ich ändern muss damit ich die UserForm in Tabelle1 angezeigt bekomme - und dabei die Tabelle2 von der die Daten ausgelesen werden ausgeblendet bleibt oder nicht sichtbar ist? Würde mich sehr freuen. Betrifft vor allem die UserForm Initialize an der wahrscheinlich eine Zeile geändert werden muss. Schönes WE René
Option Explicit
Private Sub ComboBox1_Change()
Dim SC_SL As Object
Dim vTemp As Variant
Dim iIndx As Integer
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:C" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value Then
If vTemp(iIndx, 2) "" Then SC_SL(vTemp(iIndx, 2)) = ""
End If
Next iIndx
ComboBox2.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox2.AddItem SC_SL.GetKey(iIndx)
Next iIndx
End Sub
Private Sub ComboBox2_Change()
Dim SC_SL As Object
Dim vTemp As Variant
Dim iIndx As Integer
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value And _
vTemp(iIndx, 2) = ComboBox2.Value Then
If vTemp(iIndx, 3) "" Then SC_SL(vTemp(iIndx, 3)) = ""
End If
Next iIndx
ComboBox3.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox3.AddItem SC_SL.GetKey(iIndx)
Next iIndx
End Sub
Private Sub ComboBox3_Change()
Dim WkSh As Worksheet
Dim rZelle As Range
Dim sFundst As String
Dim sSuchbegriff As String
sSuchbegriff = ComboBox3.Value
Set WkSh = ThisWorkbook.Worksheets("Tabelle2")
TextBox1.Value = ""
With WkSh.Columns(4)
Set rZelle = .Find(What:=sSuchbegriff, LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
If ComboBox1.Value = WkSh.Range("B" & rZelle.Row) And _
ComboBox2.Value = WkSh.Range("C" & rZelle.Row) Then
If TextBox1.Value = "" Then
TextBox1.Value = WkSh.Range("A" & rZelle.Row).Value
Else
TextBox1.Value = TextBox1.Value & ", " & _
WkSh.Range("A" & rZelle.Row).Value
End If
End If
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address sFundst
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim iIndx As Integer
Dim iTop As Integer
Dim vText As Variant
Dim SC_SL As Object
Dim vTemp As Variant
Worksheets("Tabelle2").Activate
vText = Array(" ", "Nachname", "Vorname", "Straße", "Nummer")
iTop = 16
For iIndx = 1 To 3
With Controls("ComboBox" & iIndx)
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
.Style = 2
End With
iTop = iTop + 30
Next iIndx
With TextBox1
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
End With
iTop = 20
For iIndx = 1 To 4
With Controls("Label" & iIndx)
.Height = 18
.Left = 12
.Top = iTop
.Width = 72
.Font.Size = 10
.Caption = vText(iIndx)
.TextAlign = 2
End With
iTop = iTop + 30
Next iIndx
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:B" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) "" Then SC_SL(vTemp(iIndx, 1)) = ""
Next iIndx
ComboBox1.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox1.AddItem SC_SL.GetKey(iIndx)
Next iIndx
End Sub