I put together a VBA procedure that does the following:

• opens a bunch of excel files (separately not at once) from a specific folder (user indicates the folder)
• excel files are password protected so it inputs the password , opens the file , then unprotects a specific sheet (as sheets are also pass protected) and does some changes to the file.

My problem is that for the changes to be applied to each excel file the sheet has to be unprotected and there is unfortunately 3 different options for pass (dont ask me why). I know the pass options so i put it in my code as per below:

However the procedure still stops if the first pass option is incorrect and I have to manually drag the execution line to the next valid pass for the procedure to continue.

Essentially my questions are: is the code set up above ok and is there a way of making VBA to attempt another password to unprotect sheet if the first pass is not the correct one (and a third attempt if the 2nd pass is also incorrect)?

Uff, hope the above makes sense and thanks for reading

CodePudding user response：

This should help. I haven't used any of your code as it's a picture that I can't copy/paste, but this should give you the idea.

Note:

• Option Explicit should appear at the top of every module - forces you to declare variables.
• the Public Const line must be at the top of a module before any procedures/functions.

Option Explicit

Public Const ERROR_SHEETPROTECTED As Long = vbObjectError   513

Sub Test()

    'Any errors should be handled by the error handler appearing after the Exit Sub.
    On Error GoTo ERROR_HANDLER

    Dim wrksht As Worksheet
    
    With ThisWorkbook
        Set wrksht = .Worksheets("Sheet1")
        
        'Use each password and exit loop when correct one is found.
        Dim PWDS As Variant
        PWDS = Array("123", "456", "ABCD")
        
        Dim PWD As Variant
        For Each PWD In PWDS
            On Error Resume Next 'Ignore any errors and move to next line.
            wrksht.Unprotect PWD
            On Error GoTo ERROR_HANDLER 'Reinstate correct error handling as soon as possible.
            If Not wrksht.ProtectContents Then Exit For 'We found a match!
        Next PWD
        
        'Final check that at least one of the passwords worked.
        If wrksht.ProtectContents Then
            Err.Raise ERROR_SHEETPROTECTED, , "Password for sheet not found"
        End If
    End With
    
Exit Sub

'Error handling appears after the main body of code (Exit Sub) and before the end of the procedure (End Sub)
ERROR_HANDLER:
    Select Case Err.Number
        Case ERROR_SHEETPROTECTED
            MsgBox Err.Number & vbCr & Err.Description, vbOKOnly   vbCritical
            'Other code to sort the problem out.
            
            Resume Next 'If problem is solved use this to jump back into the main code.
        Case Else
            MsgBox Err.Number & vbCr & Err.Description, vbOKOnly   vbCritical
    End Select
    
End Sub  

Raise method
With...End With Statement

CodePudding user response：

 On Error GoTo -1 

There are a couple of misunderstandings in your code, because even if it succeeds first time it will run into the next error handler.

But the main error is a common misunderstanding in error handling when trying to handle multiple errors in a sequenz, missing the On Error GoTo -1 :

Option Explicit

Sub BadErrorHandlingSequenz()
Dim x As Integer
On Error GoTo Errhandler1
x = 3 / 0
Exit Sub
Errhandler1:
On Error GoTo errhandler2
x = 5 / 0 ' Error is not handled
Exit Sub
errhandler2:
MsgBox "Still Error"
End Sub

Sub GoodErrorHandlingSequenz()
Dim x As Integer
On Error GoTo Errhandler1
x = 3 / 0
Exit Sub
Errhandler1:
On Error GoTo -1 ' Reset Error handling
On Error GoTo errhandler2
x = 5 / 0 ' Error is  handled
Exit Sub
errhandler2:
MsgBox "Still Error"
End Sub