Handling incorrect password runtime errors

Question

First time post but a long time user! Firstly I wanted to say thank you to every for all the code feedback you guys put on posts. It's helped me develop my VBA code more than you can imagine!

Ok so the question:

Background:
I'm developing a VBA focused addin for myself and colleagues to use. Part of this is include functions that you would except in Excel but aren't there. Some of these were quite easy to do (ie invert filters) but some are proving more difficult. This is one of those examples.

Issue:
The following code is meant to loop through the users selection of sheets, apply a user defined password or remove the existing one. Part of the function is to capture passwords that can't be removed (ie becuase the user entered an incorrect password). It works great for the first error occurrence but throughs up the runtime error (1004) for the second and repeating ones after. I don't much much experience with runtime errors handling (try to avoid errors!) but I can't get this to work. Any ideas /help to stop the runtime error popping up would be great.

Code:

Dim SHT As Worksheet, Password As String, SHT_Names(0 To 30) As String
'PREP
    'DISABLE APPLICATION FUNCTIONS
    Call Quicker_VBA(False)
    Application.EnableEvents = False

'USER PASSWORD OPTION
    Password = InputBox("Please enter a password (leave blank for no password)", "Password")

    'USER INFORMATION MESSAGES SETUP
        MSG_Protect = "Added to-"
        Protect_check = MSG_Protect

        MSG_Unprotect = "Removed from-"
        Unprotect_check = MSG_Unprotect

        MSG_unable = "Unable to remove protection from-"
        Unable_check = MSG_unable

    'ID SHEETS SELECTED
        For Each SHT In ActiveWindow.SelectedSheets
            a = a + 1
            SHT.Activate
            SHT_Names(a) = SHT.name
        Next

'MAIN
    HomeSHT = ActiveSheet.name

    'PROTECT SHEETS SELECTED BY USER
        For b = 1 To a
            Sheets(SHT_Names(b)).Select
            Set SHT = ActiveSheet

            'ENABLE OR REMOVE PROTECTION FROM SELECTED SHEET
            If SHT.ProtectContents Then
                On Error GoTo Password_FAIL
                Application.DisplayAlerts = False
                SHT.Unprotect Password
                On Error GoTo 0

                MSG_Unprotect = MSG_Unprotect & vbNewLine & Chr(149) & " " & SHT.name
            Else:

                'ENABLE FILTER CHECK
                FilterOn = False
                If ActiveSheet.AutoFilterMode Then FilterOn = True

                'PROTECT SHEET
                SHT.Protect Password, AllowFiltering:=FilterOn

                'UPDATE USER MESSAGE
                MSG_Protect = MSG_Protect & vbNewLine & Chr(149) & " " & SHT.name & " - Users can: Select locked and unlocked cells"
                If FilterOn = True Then MSG_Protect = MSG_Protect & " and use filters"
            End If
200     Next

'INFORM USER
    If Protect_check <> MSG_Protect Then msg = MSG_Protect & vbNewLine & "___________________" & vbNewLine
    If Unprotect_check <> MSG_Unprotect Then msg = msg & MSG_Unprotect & vbNewLine & "___________________" & vbNewLine
    If Unable_check <> MSG_unable Then msg = msg & MSG_unable

    MsgBox msg, , "Protection summary"

'TIDY UP
    Sheets(HomeSHT).Activate
    'ENABLE APPLICATION FUNCTIONS
    Call Quicker_VBA(True)
Exit Sub
Password_FAIL:
    MSG_unable = MSG_unable & vbNewLine & Chr(149) & " " & SHT.name
    Application.EnableEvents = False
    GoTo 200
End Sub

Nothing jumps out as obviously incorrect, but I suspect it may have to do with the use of the [ActiveSheet and Select](https://stackoverflow.com/q/10714251/4088852). — Comintern, Oct 12 '18 at 12:35
It's occurs for me on the 2nd occurrence where a protected sheet (with an incorrect password). — Iain Harper, Oct 12 '18 at 12:39

score 0 · Answer 1 · answered Oct 12 '18 at 17:39

At a quick glance, it seems that the problem is in the way you're handling your errors. You use the line On Error GoTo Password_FAIL to jump down to the error handler. The error handler logs some information and then jumps up to label '200'. I can't tell if the formatting is off, but it looks like the label for '200' points to Next, indicating that the loop should continue with the next sheet.

So, where's the problem? You never actually reset the original error. Three lines below On Error GoTo Password_FAIL you explicitly call On Error GoTo 0 to reset the error handler, but that line will never actually be reached in an error. The program will jump to the error handler, and then from there jump up to the loop iterator. Using the GoTo statement for control flow can easily lead to these types of issues, which is why most developers recommend against it.

I'll post some sample code below to show a different (potentially better) way to handle code exceptions. In the sample below, the code simply loops through all of the worksheets in the workbook and toggles the protection. I didn't include much of your logging, or the constraint that only the selected sheets be toggled. I wanted to focus on the error handling instead. Besides, from reading you code, it seems that you can manage more of the peripheral details. Send a message if there's still some confusion

Sub ToggleProtectionAllSheets()
    Dim sht As Worksheet
    Dim password As String

    On Error Resume Next

    password = InputBox("Please enter a password (leave blank for no password)", "Password")
    For Each sht In ActiveWorkbook.Worksheets
        If sht.ProtectContents Then
            sht.Unprotect password

            If Err.Number <> 0 Then
                Err.Clear
                MsgBox "Something did not work according to plan unprotecting the sheet"
            End If
        Else
            sht.Protect password

            If Err.Number <> 0 Then
                Err.Clear
                MsgBox "Something went wrong with protection"
            End If
        End If

    Next sht
End Sub

Handling incorrect password runtime errors

1 Answers1