Check for existence

CWillis · 05-30-2006, 04:00 PM

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.

**mrice** · 05-30-2006, 04:24 PM

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

Dave Peterson · 05-30-2006, 06:35 PM

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

CWillis · 05-31-2006, 08:25 AM

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
>