Resetting VBA by Coding

[sassriverrat]

So I'm stuck trying to figure a workaround to this-

I've created this fantastic, dynamic workbook with awesome help from many of you. It seems to be working incredibly well, minus a few minute bugs. Anyway, because the users aren't VBA friendly at all (really not even formula friendly), I've been trying to figure out a resetting method for them.

Is there some type of error message/resetting system so that instead of excel trying to run the debugger or end the code, it will popup a window that says something like "Incorrect Use, Please Try Again", undo whatever coding was done, and allow the user to try to execute whatever he or she was doing again? I know that might sound convoluted and I'm sure just continuing to error check my code through testing is beneficial but people somehow always find ways to create a mess periodically.

Thanks!

 

[mole999]

you could code a full page reset should the sheet fall over, and i would run that on close, so if opened with macros disabled then it will have already been fixed, maybe. not enough examples to understand what you actually need to understand scenarios

 

[sassriverrat]

So I have a "Reset" button that wipes the values from selected cells and reloads the formulas- but what I'm looking for is a way to prevent the Error message that asks to end or debug- so instead of having the debug option I just want excel to automatically choose "End" and then display a message instead. Basically I don't want the user to have the ability to go through the debug menu.

 

[mikerickson]

You do not want that debug screen to go away. It is your friend and lets you know when your coding needs improvement.

What you should do is code so that no matter what fool thing the user might do, the code doesn't error out.
The user doing silly things shouldn't necessarily lead to something useful, but it shouldn't cause a debug error. It is the coders responsibility to keep that from happening.
This process is sometimes known as "gorilla proofing".

"Garbage In, Garbage Out" is different than "Garbage In, Dump-truck Breaks"

 

[sassriverrat]

Touche.

So, for example, I have a button that creates a sheet named "Noon" and formats it. If you hit the button after the "Noon" has been created, you obviously create an error. I guess I need to figure out how to write a piece in there to not write the sheet and give me a message like "The Noon Sheet already exists. Would you like to delete it before creating a new one?" so it can't bug out.

Also, how would I tell Excel to delete all sheets with the name "Noon" in them when closing (so one can't save a "Noon" in the template).

 

[mikerickson]

To see if there is a worksheet named Noon:

On Error Resume Next
If Sheets("Noon").Name <> "Noon" Then
    MsgBox "no Noon sheet"
Else
    MsgBox "its there"
End If
On Error Goto 0

 

[sassriverrat]

hmm...where exactly do I insert this within my code?
I presume in the beginning of the "sheet creator" since the first lines of the "sheet creator" are

'Adds additional Noon Sheets    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Noon" & (ActiveSheet.Index - 4

and if it were after this it wouldn't work, correct? It would get stuck and want to debug before hitting this point...

 

[mikerickson]

Checking to see if a sheet exists is a common occurrence, it would be good to create a UDF.

Function SheetExists(sheetName as String, optional wb as Workbook) As Boolean
    If wb Is Nothing then Set wb=Thisworkbook

    On Error Resume Next
    SheetExists = (LCase(ws.Sheets(sheetName).Name) = LCase(sheetName))
    On Error Goto 0
Exit Function

Then that can be called when ever you need to check about a sheet and the result can be used to branch to the appropriate action.

 

[sassriverrat]

Understood. I'll run it and see what happens. I also wrote a quick piece that kills any sheets named *** (the ones that get added) when the workbook is closed if the workbook is named "Master Template"

 

[sassriverrat]

hmmm
@mikerickson

So to make sure I absolutely understand that function- I read it as
if the sheet exists (checks name of sheet- I need to replace "sheetname" with my sheet,
then on the error top it.

If this is right, could I add this (below) right before "On Error Goto 0"?

msgbox("You are trying to create a sheet that already exists, please use or delete that version of the sheet first")