Hallo Olli,
teste mal:
Private Sub CheckBox1_Click()
With Worksheets("Tabelle1") 'Blattname anpassen
Me.TextBox4 = ""
'Daten aus Spalte L in Textbox
If Me.CheckBox1 And Not Me.CheckBox2 And Me.ComboBox1.ListIndex > -1 Then
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:L"), 12, False), "0.00")
'Daten aus Spalte O in Textbox
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:O"), 15, False), "0.00")
Me.TextBox3 = ""
ElseIf Me.CheckBox2 And Not Me.CheckBox1 And Me.ComboBox1.ListIndex > -1 Then
'Daten aus Spalte M in Textbox1
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:M"), 13, False), "0.00")
'Daten aus Spalte K in Textbox2
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:K"), 11, False), "0.00")
'Daten aus Spalte P in Textbox3
Me.TextBox3 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:P"), 16, False), "0.00")
ElseIf Me.CheckBox1 And Me.CheckBox2 And Me.ComboBox1.ListIndex > -1 Then
'Daten aus Spalte N in Textbox1
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:N"), 14, False), "0.00")
'Daten aus Spalte O in Textbox2
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:O"), 15, False), "0.00")
'Daten aus Spalte R in Textbox3
Me.TextBox3 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:R"), 18, False), "0.00")
Else
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
End If
End With
End Sub
Private Sub CheckBox2_Click()
With Worksheets("Tabelle1") 'Blattname anpassen
Me.TextBox4 = ""
If Me.CheckBox2 And Not Me.CheckBox1 And Me.ComboBox1.ListIndex > -1 Then
'Daten aus Spalte M in Textbox1
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:M"), 13, False), "0.00")
'Daten aus Spalte K in Textbox2
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:K"), 11, False), "0.00")
'Daten aus Spalte P in Textbox3
Me.TextBox3 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:P"), 16, False), "0.00")
ElseIf Me.CheckBox1 And Not Me.CheckBox2 And Me.ComboBox1.ListIndex > -1 Then
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:L"), 12, False), "0.00")
'Daten aus Spalte O in Textbox
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:O"), 15, False), "0.00")
Me.TextBox3 = ""
ElseIf Me.CheckBox2 And Me.CheckBox1 And Me.ComboBox1.ListIndex > -1 Then
'Daten aus Spalte N in Textbox1
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:N"), 14, False), "0.00")
'Daten aus Spalte O in Textbox2
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:O"), 15, False), "0.00")
'Daten aus Spalte R in Textbox3
Me.TextBox3 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:R"), 18, False), "0.00")
Else
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
End If
End With
End Sub
Private Sub ComboBox1_Change()
With Worksheets("Tabelle1") 'Blattname anpassen
If Me.ComboBox1 = "" Then
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Exit Sub
End If
Me.TextBox4 = ""
If Me.CheckBox1 And Not Me.CheckBox2 Then
'Daten aus Spalte L in Textbox1
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:L"), 12, False), "0.00")
'Daten aus Spalte O in Textbox2
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:O"), 15, False), "0.00")
Me.TextBox3 = ""
ElseIf Me.CheckBox2 And Not Me.CheckBox1 Then
'Daten aus Spalte M in Textbox1
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:M"), 13, False), "0.00")
'Daten aus Spalte K in Textbox2
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:K"), 11, False), "0.00")
'Daten aus Spalte P in Textbox3
Me.TextBox3 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:P"), 16, False), "0.00")
ElseIf Me.CheckBox1 And Me.CheckBox2 Then
'Daten aus Spalte N in Textbox1
Me.TextBox1 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:N"), 14, False), "0.00")
'Daten aus Spalte O in Textbox2
Me.TextBox2 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:O"), 15, False), "0.00")
'Daten aus Spalte R in Textbox3
Me.TextBox3 = Format(WorksheetFunction.VLookup(Me.ComboBox1, .Range("A:R"), 18, False), "0.00")
End If
End With
End Sub
Private Sub CommandButton1_Click()
If Not Me.CheckBox1 And Not Me.CheckBox2 Then
MsgBox "Es wurde keine Auswahl getroffen."
Exit Sub
End If
If Me.CheckBox1 And Not Me.CheckBox2 And Me.ComboBox1.ListIndex > -1 Then
Me.TextBox4 = Format(CDbl(Me.TextBox2), "0.00")
'End If
ElseIf Me.CheckBox1 And Me.CheckBox2 And Me.ComboBox1.ListIndex > -1 Then
Me.TextBox4 = Format(CDbl(Me.TextBox2) + CDbl(Me.TextBox3), "0.00")
'End If
ElseIf Me.CheckBox2 And Not CheckBox1 And Me.ComboBox1.ListIndex > -1 Then
Me.TextBox4 = Format(CDbl(Me.TextBox2) + CDbl(Me.TextBox3), "0.00")
Else
MsgBox "Bitte ein Schiff auswählen."
End If
End Sub
Der Code gehört komplett ins Codemodul deiner Userform. Die Namen deiner Textboxen/Checkboxen/Combobox und des Tabellenblattes mußt du ggf. noch an deine Verhältnisse anpassen.
Gruß Werner
|