Option Explicit

[dumitrudan608]

Hi there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am working on a template for a report, and I got stuck with something.<o></o>
<o> </o>
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></o>
<o> </o>
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></o>
<o> </o>
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></o>
<o> </o>
“On error resume next” does not work in this case, or maybe I did not use it right.<o></o>

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

 

[Andrew Poulsom]

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.

 

[dumitrudan608]

If i put the macro in another module, and not use Option Explicit, will it still give me that error?

 

[Andrew Poulsom]

Why don't you try it and see?

 

[dumitrudan608]

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

 

[Colin Legg]

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