I am pasting a text box using a macro. I would like for the macro to make
sure that a text box doesn't already exist under the same name.
If it does, delete it and then paste the new one.
If it doesn't, paste the new one.
Thanks in advance.

Try this...


Sub Macro1()
For Each Shape In ActiveSheet.Shapes
If Shape.Name = "MyTextBoxName" Then
Shape.delete
End If
Next Shape

Insert your PASTE code here


End Sub


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546853

You could check first, but if you're going to delete it, why not just delete it?

'if the textbox is from the Drawing toolbar
On Error Resume Next
ActiveSheet.TextBoxes("text box 1").Delete
On Error GoTo 0

'if the textbox is from the control toolbox toolbar
On Error Resume Next
ActiveSheet.OLEObjects("textbox1").Delete
On Error GoTo 0

If the textbox isn't there to be deleted, the "on error resume next" line will
tell excel to ignore the error.

But you could just move/resize the existing one...



CWillis wrote:

I am pasting a text box using a macro. I would like for the macro to make
sure that a text box doesn't already exist under the same name.
If it does, delete it and then paste the new one.
If it doesn't, paste the new one.
Thanks in advance.

--

Dave Peterson

Thank you! Both will work. Plus some of that code will help with something
I have later. Thanks again.

"Dave Peterson" wrote:

You could check first, but if you're going to delete it, why not just delete it?

'if the textbox is from the Drawing toolbar
On Error Resume Next
ActiveSheet.TextBoxes("text box 1").Delete
On Error GoTo 0

'if the textbox is from the control toolbox toolbar
On Error Resume Next
ActiveSheet.OLEObjects("textbox1").Delete
On Error GoTo 0

If the textbox isn't there to be deleted, the "on error resume next" line will
tell excel to ignore the error.

But you could just move/resize the existing one...



CWillis wrote:

I am pasting a text box using a macro. I would like for the macro to make
sure that a text box doesn't already exist under the same name.
If it does, delete it and then paste the new one.
If it doesn't, paste the new one.
Thanks in advance.

--

Dave Peterson