Hallo Thomas,
teste mal:
Option Explicit
Private mavntValues As Variant
Private Sub ComboBox1_Change()
Dim lngIndex As Long
Dim ialngIndex As Long
For lngIndex = 2 To 6
Call Controls("ComboBox" & CStr(lngIndex)).Clear
Next
If ComboBox1.ListIndex > -1 Then
For ialngIndex = LBound(mavntValues, 1) To UBound(mavntValues, 1)
If Not IsEmpty(mavntValues(ialngIndex, 2)) Then If mavntValues(ialngIndex, 1) = ComboBox1.Text Then _
Call ComboBox2.AddItem(mavntValues(ialngIndex, 2))
Next
End If
End Sub
Private Sub ComboBox2_Change()
Dim lngIndex As Long
Dim ialngIndex As Long
For lngIndex = 3 To 6
Call Controls("ComboBox" & CStr(lngIndex)).Clear
Next
If ComboBox2.ListIndex > -1 Then
For ialngIndex = LBound(mavntValues, 1) To UBound(mavntValues, 1)
If Not IsEmpty(mavntValues(ialngIndex, 3)) Then If mavntValues(ialngIndex, 1) = ComboBox1.Text And _
mavntValues(ialngIndex, 2) = ComboBox2.Text Then Call ComboBox3.AddItem(mavntValues(ialngIndex, 3))
Next
End If
End Sub
Private Sub ComboBox3_Change()
Dim lngIndex As Long
Dim ialngIndex As Long
For lngIndex = 4 To 6
Call Controls("ComboBox" & CStr(lngIndex)).Clear
Next
If ComboBox3.ListIndex > -1 Then
For ialngIndex = LBound(mavntValues, 1) To UBound(mavntValues, 1)
If Not IsEmpty(mavntValues(ialngIndex, 4)) Then If mavntValues(ialngIndex, 1) = ComboBox1.Text And _
mavntValues(ialngIndex, 2) = ComboBox2.Text And mavntValues(ialngIndex, 3) = ComboBox3.Text Then _
Call ComboBox4.AddItem(mavntValues(ialngIndex, 4))
Next
End If
End Sub
Private Sub ComboBox4_Change()
Dim lngIndex As Long
Dim ialngIndex As Long
For lngIndex = 5 To 6
Call Controls("ComboBox" & CStr(lngIndex)).Clear
Next
If ComboBox4.ListIndex > -1 Then
For ialngIndex = LBound(mavntValues, 1) To UBound(mavntValues, 1)
If Not IsEmpty(mavntValues(ialngIndex, 5)) Then If mavntValues(ialngIndex, 1) = ComboBox1.Text And _
mavntValues(ialngIndex, 2) = ComboBox2.Text And mavntValues(ialngIndex, 3) = ComboBox3.Text And _
mavntValues(ialngIndex, 4) = ComboBox4.Text Then Call ComboBox5.AddItem(mavntValues(ialngIndex, 5))
Next
End If
End Sub
Private Sub ComboBox5_Change()
Dim lngIndex As Long
Dim ialngIndex As Long
For lngIndex = 6 To 6
Call Controls("ComboBox" & CStr(lngIndex)).Clear
Next
If ComboBox5.ListIndex > -1 Then
For ialngIndex = LBound(mavntValues, 1) To UBound(mavntValues, 1)
If Not IsEmpty(mavntValues(ialngIndex, 6)) Then If mavntValues(ialngIndex, 1) = ComboBox1.Text And _
mavntValues(ialngIndex, 2) = ComboBox2.Text And mavntValues(ialngIndex, 3) = ComboBox3.Text And _
mavntValues(ialngIndex, 4) = ComboBox4.Text And mavntValues(ialngIndex, 5) = ComboBox5.Text Then _
Call ComboBox6.AddItem(mavntValues(ialngIndex, 6))
Next
End If
End Sub
Private Sub ComboBox6_Change()
Dim ialngIndex As Long
If ComboBox6.ListIndex > -1 Then
For ialngIndex = LBound(mavntValues, 1) To UBound(mavntValues, 1)
If mavntValues(ialngIndex, 1) = ComboBox1.Text And mavntValues(ialngIndex, 2) = ComboBox2.Text And _
mavntValues(ialngIndex, 3) = ComboBox3.Text And mavntValues(ialngIndex, 4) = ComboBox4.Text And _
mavntValues(ialngIndex, 5) = ComboBox5.Text And mavntValues(ialngIndex, 6) = ComboBox6.Text Then _
TextBox1.Text = mavntValues(ialngIndex, 7)
Next
End If
End Sub
Private Sub UserForm_Initialize()
Dim ialngIndex As Long
Dim objDictionary As Object
With Tabelle1
mavntValues = .Range(.Cells(7, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 8)).Value2
End With
ReDim Preserve mavntValues(LBound(mavntValues, 1) To UBound(mavntValues, 1), LBound(mavntValues, 2) To 8)
Set objDictionary = CreateObject(Class:="Scripting.Dictionary")
For ialngIndex = LBound(mavntValues, 1) To UBound(mavntValues, 1)
mavntValues(ialngIndex, 8) = 6 + ialngIndex
If Not IsEmpty(mavntValues(ialngIndex, 1)) Then objDictionary(mavntValues(ialngIndex, 1)) = vbNullString
Next
ComboBox1.List = objDictionary.Keys
Set objDictionary = Nothing
End Sub
Gruß
Nepumuk