VBA code doesn't unprotect sheet but Excel does

**tlewis3348** · 08-19-2009, 10:54 AM

I am working with several workbooks that are protected. I want to loop through them, do some stuff on each one and print out a copy. I have inserted code to unprotect the sheet with the correct password. However, an error occurs when the code tries to edit the workbook because, in reality, it has not been unprotected at all. While in debug mode, I can go and unprotect the sheet manually with Excel (with the same password) and it is unprotected. However, I inserted an error handler that tries to unprotect the sheet again. This causes the code to just continuously loop and never unprotects the sheet. Here is my code:

Option Explicit
Sub Test()

Application.ScreenUpdating = False

Dim intCurrentFile As Integer
Dim strDocPath As String
Dim strCurrentFile As String
Dim booStuff As Boolean

For intCurrentFile = 1 To 48
    strDocPath = Workbooks("Personal.xls").Worksheets("Sheet1").Cells(intCurrentFile, 1).Value 'refers to a worksheet with a list of directory names to loop through
    strCurrentFile = Dir(strDocPath & "\*")
    Do While strCurrentFile <> ""
        strDocPath = Workbooks("Personal.xls").Worksheets("Sheet1").Cells(intCurrentFile, 1).Value
        Workbooks.Open Filename:=strDocPath & "\" & strCurrentFile
        
        On Error GoTo ErrorHandler1
        booStuff = Cells.Find(What:="Stuff", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Validation.Value 'sets the variable to true when "Stuff" is found on the active worksheet
        On Error GoTo 0
        
        If booStuff = True Then 'inserts "Stuff" in other worksheet when it is present in the current one
            strDocPath = Replace(strDocPath, "Folder1", "Folder2")
            Application.DisplayAlerts = False 'Prevents "Save changes?" message
            ActiveWorkbook.Close SaveChanges:=True
            Application.DisplayAlerts = True
            Workbooks.Open Filename:=strDocPath & "\" & strCurrentFile
            Workbooks(strCurrentFile).Unprotect Password:="password" '<<-- Unlocks worksheet
            On Error GoTo ErrorHandler2
33:         Range("D32") = "Stuff" '<<-- Worksheet not unlocked
            On Error GoTo 0
                    
            'Print sample
        
                ActiveWorkbook.PrintOut
        
            'Close
            
                Application.DisplayAlerts = False
                ActiveWorkbook.Close SaveChanges:=True
                Application.DisplayAlerts = True

        End If
        
            On Error Resume Next
            Application.DisplayAlerts = False
            ActiveWorkbook.Close
            Application.DisplayAlerts = True
            On Error GoTo 0
            
            strCurrentFile = Dir
        
    Loop
Next intCurrentBatchSheet

Application.ScreenUpdating = True

Exit Sub

ErrorHandler1:
    booPigmentLeft = False
    Err.Clear
    Resume Next
    
ErrorHandler2:
    ActiveWorkbook.Unprotect "password" '<<-- Unlocks worksheet
    Resume 33 'Goes back to the line that caused the error
    
End Sub

Any help would be greatly appreciated. Thanks.

**Phil_V** · 08-19-2009, 11:21 AM

Workbooks(strCurrentFile).Unprotect Password:="password" '<<-- Unlocks worksheet

Your comment says it unlocks the worksheet... which worksheet? You need to specify which worksheet you are trying to unlock

Equally you will need to specifiy which sheet you are referring to with this statment:

Range("D32") = "Stuff"

Something like this:

Workbooks.Open Filename:=strDocPath & "\" & strCurrentFile
    With Workbooks(strCurrentFile).Worksheets("MySheet")
        .Unprotect Password:="password" '<<-- Unlocks worksheet
        .Range("D32") = "Stuff"                     
    End With
    'Print sample
    ActiveWorkbook.PrintOut
        
    'Close