zwei ComboBoxen
11.07.2021 15:05:17
Bernd
Sub Workbook_Open()
Z = 2
a = Sheets("T2").Cells(Z, 2).Value
While a ""
Sheets("T1").ComboBox1.AddItem a
Z = Z + 1
a = Sheets("T2").Cells(Z, 2).Value
Wend
End Sub Sub Workbook_Open1()
Z = 2
a = Sheets("T3").Cells(Z, 2).Value
While a ""
Sheets("T1").ComboBox1.AddItem a
Z = Z + 1
a = Sheets("T3").Cells(Z, 2).Value
Wend
End Sub
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
On Error Resume Next
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then shp.Delete
Next
Range("a1").Select
Set c = Worksheets("T2").Columns(2).Find(ComboBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Range("B8").Value = c.Offset(0, 0) '
Range("D8").Value = c.Offset(0, 1) '
Range("F8").Value = c.Offset(0, 2) '
With Range("C:C")
End With
Range("a1").Select
End If
End Sub
Private Sub ComboBox2_Change()
Application.ScreenUpdating = False
On Error Resume Next
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then shp.Delete
Next
Range("a1").Select
Set c = Worksheets("T3").Columns(2).Find(ComboBox2.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Range("B8").Value = c.Offset(0, 0) '
Range("D8").Value = c.Offset(0, 1) '
Range("F8").Value = c.Offset(0, 2) '
With Range("C:C")
End With
Range("a1").Select
End If
End Sub
Private Sub CommandButton1_Click()
Range("B8").Select
Selection.ClearContents
Range("D8").Select
Selection.ClearContents
Range("F8").Select
Selection.ClearContents
Range("A1").Select
End Sub
Bernd
Anzeige