Delete command button

[DocBrown]

I have a template which has a command button on a sheet. What I want to do is
when a user creates a NEW workbook from the template, I want the button to
exist. Then the next time they open the workbook, I want the button to be
deleted from the template sheet and any copies they've made of the sheet.

The sticky part is once the button is deleted, the next time the the file is
opened again, the control is gone so I get a runtime error because the
control doesn't exist. How can I check if the control exists before I try to
delete it.

Or is it just best to do the On Error Resume Next thing?

Thanks,
John

 

[JLGWhiz]

I didn't see any code posted.

 

[Dave Peterson]

Give that commandbutton a nice unique name (I used CMDOneTime.)

Then put this in your Autpen routine:

Option Explicit
Sub auto_open()

Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
wks.OLEObjects("CMDOneTime").Delete
Next wks
On Error Resume Next

End Sub

You could use something like this to check for the commandbutton's existance
first:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
Dim OLEObj As OLEObject

For Each wks In ThisWorkbook.Worksheets
Set OLEObj = Nothing
On Error Resume Next
Set OLEObj = wks.OLEObjects("CMDOneTime")
On Error GoTo 0

If OLEObj Is Nothing Then
'not there, do nothing
Else
OLEObj.Delete
End If
Next wks

End Sub

But if you're gonna use "on error resume next", why bother with the extra check
first?