Hi

I would like to be able to delete a shape depending on the value in a cell.

ActiveSheet.Shapes("TextBox2").Delete - works.

If [A2] = 1 then I would like to delete TextBox1.
If [A2] = 2 then I would like to delete TextBox2.


This doesn't work:

ActiveSheet.Shapes("TextBox" & [A2]).Delete

or

S1 = "TextBox" & [A2]

ActiveSheet.Shapes(S1).Delete


Can somebody help me with this ?

TIA

Andrew B

Hi Andrew,
Assuming the cell which performs control is A1,
In the worksheet module, have following
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 1 Then
TextBox1.Visible = True
Else
TextBox1.Visible = False
End If
End Sub

HTH
Carim

Thanks for your reply but this is not what I am after.


Carim wrote:
Hi Andrew,
Assuming the cell which performs control is A1,
In the worksheet module, have following
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 1 Then
TextBox1.Visible = True
Else
TextBox1.Visible = False
End If
End Sub

HTH
Carim

answered too quickly ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
i = Range("A2").Value
ActiveSheet.Shapes("TextBox" & i).Delete
End Sub

Thanks

Carim wrote:
answered too quickly ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
i = Range("A2").Value
ActiveSheet.Shapes("TextBox" & i).Delete
End Sub

If you say this works:
ActiveSheet.Shapes("TextBox2").Delete

Then what you proposed originally worked fine for me as seen from the
immediate window:

? activesheet.shapes(1).Name
Textbox2
? Range("A1").Value
2
? [a1]
2
? Activesheet.Shapes("Textbox" & [A1]).Name
Textbox2
activesheet.Shapes("Textbox" & [A1]).Delete
---------------------

Textbox2 is deleted

Tested with both a Textbox from the drawing toolbar and one from the control
toolbox toolbar.

--
Regards,
Tom Ogilvy


"Andrew B" wrote in message
...
Thanks

Carim wrote:
answered too quickly ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
i = Range("A2").Value
ActiveSheet.Shapes("TextBox" & i).Delete
End Sub