Option Explicit

dumitrudan608

New Member
Joined
Dec 7, 2010
Messages
19
Hi there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I am working on a template for a report, and I got stuck with something.<o:p></o:p>
<o:p> </o:p>
I have the function "Option Explicit" activated, and the last of the macros must search for six buttons in three sheets (2 in each sheet) and delete them. The thing is some people who will use this template may "accidentally" delete one of the sheets, in which case the macro will show a "variable not defined" error for... let's say sheet3 (if sheet3 has been deleted)<o:p></o:p>
<o:p> </o:p>
I chose to cal the sheets not by their caption but by their internal name (I use sheet2 instead of sheets("licenses") because the same people may change their name.<o:p></o:p>
<o:p> </o:p>
Is there any way to check if sheet exists and only if the sheet exists delete the 2 buttons? All I want is to skip the “variable not defined” error.<o:p></o:p>
<o:p> </o:p>
“On error resume next” does not work in this case, or maybe I did not use it right.<o:p></o:p>

this is the code:
On Error Resume Next
If MsgBox("Are you sure you want to erase work buttons?", vbYesNo) = vbYes Then
If Not sheet2 Is Nothing Then
sheet2.Shapes("CommandButton1").Delete
sheet2.Shapes("CommandButton2").Delete
End If

If Not sheet3 Is Nothing Then
sheet3.Shapes("CommandButton1").Delete
sheet3.Shapes("CommandButton2").Delete
End If

If Not sheet4 Is Nothing Then
sheet4.Shapes("CommandButton1").Delete
sheet4.Shapes("CommandButton2").Delete
End If
End If

If MsgBox("Do you want to rename curent sheet?", vbYesNo) = vbYes Then
CurrentSheetName = InputBox("Rename your current sheet: ", , "License Entitlement")
ActiveSheet.Name = CurrentSheetName
Else
CurrentSheetName = ActiveSheet.Name
End If

On Error GoTo 0

Hope you can help me with this,

Best regards,
Daniel
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Welcome to the Board.

You will get a compile error (rather than a run-time error) if the sheet doesn't exist and I don't think that can be avoided without removing Option Explicit.
 
Upvote 0
Thank you for your help, putting it in another module worked.
Sometimes it is a payne to have to think of every mistake people who don't know a thing about excel, macros and databases might do.

Best regards,
Daniel
 
Upvote 0
Perhaps you could protect the workbook structure to prevent worksheets from being deleted? My recommendation is that you should always include Option Explicit.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top