Option Explicit

dumitrudan608

New Member
Joined
Dec 7, 2010
Messages
15
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Dec 7, 2010
Messages
15
If i put the macro in another module, and not use Option Explicit, will it still give me that error?
 

dumitrudan608

New Member
Joined
Dec 7, 2010
Messages
15
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

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Perhaps you could protect the workbook structure to prevent worksheets from being deleted? My recommendation is that you should always include Option Explicit.
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top