AW: Ergänzung
20.05.2019 13:33:26
cysu11
Hallo Markus,
oder du schließt den Dropdown nach Name aus!
Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim shp As Shape
Dim txt As Shape
Dim L?ngePal As Integer
Dim BreitePal As Integer
Dim L?ngeBox As Integer
Dim BreiteBox As Integer
Set KeyCells = Range("B3:B4")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
For Each shp In ActiveSheet.Shapes
If shp.Name "Drop Down 1" Then
MsgBox shp.Name
If Intersect(shp.TopLeftCell, Range("A1:I1")) Is Nothing And shp.Type = msoAutoShape Then _
shp.Delete
End If
Next shp
For Each txt In ActiveSheet.Shapes
MsgBox txt.Name
If txt.Type = msoTextBox Then txt.Delete
Next txt
L?ngePal = Range("A8").Value / 5
BreitePal = Range("A9").Value / 5
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 200, 50, L?ngePal, BreitePal).Name = "Palette"
ActiveSheet.Shapes("Palette").Fill.ForeColor.RGB = RGB(255, 228, 181)
ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 200 + (L?ngePal / 2.8), 33, 60, 150). _
TextFrame.Characters.Text = Range("A8").Value & " mm"
ActiveSheet.Shapes.AddLabel(msoTextOrientationVerticalFarEast, 147, 50 + (BreitePal / 2.8), 60, _
60).TextFrame.Characters.Text = Range("A9").Value & " mm"
L?ngeBox = Range("B8").Value / 5
BreiteBox = Range("B9").Value / 5
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 500, 50, L?ngeBox, BreiteBox).Name = "Beh?lter"
ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 500, 33, 60, 150).TextFrame. _
Characters.Text = Range("B8").Value & " mm"
ActiveSheet.Shapes.AddLabel(msoTextOrientationVerticalFarEast, 448, 50, 60, 60).TextFrame. _
Characters.Text = Range("B9").Value & " mm"
End If
End Sub
LG
Alexandra