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!

 

[mikerickson]

I wouldn't but the warning msgbox inside the function.

I'm imagining a scenario where you are running a macro to add a sheet named Smith, sometimes it already exists, sometimes not.

I wouldn't want to interrupt the user by forcing them to respond to the warning message every time the macro is run.

I would put the warning message outside of the function, in the calling routine, that way you can control when it appears rather then "always"

If SheetExists("Smith") Then
    MsgBox "that already exitsts"
Else
    ' code to make new sheet
End If

 

[sassriverrat]

So I tweaked your code and obviously haven't done something right-
Notes:
I renamed the code to make the new sheet into NoonSheetCreate so I could call it separately and space some stuff out.
Also- I tried your original sheetexists function and it error'd wanting an "End Function" instead of "Exit" I have it the end function.

'Error Checks for a Noon Sheet already existing    If SheetExists("Noon") Then
        MsgBox "Noon Sheet already exists, would you like to delete it and start over?", vbYesNoCancel
        If vbYes Then
            Sheets("Noon").Delete
            Call NoonSheetCreate
        ElseIf vbNo Then
            Sheets("Noon").Select
            Exit Sub
        End If
    Else: Call NoonSheetCreate
    End If
End Sub

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
End Function

 

[mikerickson]

The line in the SheetsExists function should be

SheetExists = (LCase(w[COLOR="#FF0000"]b[/COLOR].Sheets(sheetname).Name) = LCase(sheetname))

 

[sassriverrat]

@mikerickson
You sir, are a guru, an expert!

Thank you!
Wow this is awesome!

It is amazing how much I have learned in the past few weeks (hour or two a day when I can). I've totally taken a new approach and made a workbook go from "boring" to running just about like a program! This IS cool! Now it's becoming like an addiction, ever improving, advancing, and bettering the code.