Resetting VBA by Coding

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
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!
 
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"

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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
Code:
'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
Code:
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
 
Upvote 0
The line in the SheetsExists function should be
Code:
SheetExists = (LCase(w[COLOR="#FF0000"]b[/COLOR].Sheets(sheetname).Name) = LCase(sheetname))
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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