Deleting Shapes

[Dean81]

Hello all!

I have a macro that will add text boxes to a form template depending on the contents of cell D4:

If Range("D4").Value = "OGD" Then
ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=3.75, Top:=270, Width:=333.75, Height:= _
        62.25).Select
    Selection.Name = "1"

It goes on to add another 6 text boxes to the template. If D4="FS" then it adds another 8 boxes. I want to be able to delete the boxes using a macro but can't work out how to tell it that if boxes 8 and 9 are there then delete them and if not then carry on. At the moment it gives me an error message if 8 and 9 aren't there.

I have other shapes in there so I can't do delete all shapes.

I hope that makes sense!

Thanks,

Dean

[Richard Buttrey]

Hi,

If it helps I recently wrote the following code to delete all shapes on a sheet - with exceptions as necessary. You may be able to adapt it.

Sub RemoveSHapes()
    Dim wShape As Object
   
   ' the exceptions are 'drop' = drop down cells like for example validation drop downs
   ' 'labe' = Label
   ' 'comb' = Combo Box
   ' 'opti' = Option Button
    For Each wShape In Sheet2.Shapes
        If LCase(Left(wShape.Name, 4)) <> "drop" And _
           LCase(Left(wShape.Name, 4)) <> "labe" And _
           LCase(Left(wShape.Name, 4)) <> "comb" And _
           LCase(Left(wShape.Name, 4)) <> "opti" Then
            wShape.Delete
        End If
    Next wShape
  
End Sub

[Dean81]

Thanks Richard. Unfortunately I can't get it to work. Could be due to my editing!

What I want is the macro equivelent of saying to someone "If you find the text box called 8 delete it, if not, no worries"

The rest of this spreadsheet is working really well and this is leterally the last thing I need to do to it! So frustrating!

[Richard Buttrey]

Hi,

In that case just

Sub DeleteTestBox8()
 Dim wShape As Object
   
    For Each wShape In ActiveSheet.Shapes
        If wShape.Name = "TextBox8" Then
            wShape.Delete
        End If
    Next wShape
End Sub

[snb]

Why so complicated ?

You can have all these ActiveX-controls in the worksheet, set to visible=false.
If you need them to be visible dependent on the values in the worksheet you can use .visible=true.