Here's code to loop through all (Excel) files in a folder, and set protection on / off, based on the parameters passed to the subroutine:
Sub ToggleProtection(stPath As String, stPassword As String, bProtect As Boolean)
Dim stFile As String, wb As Workbook, lPass As Long, lFail As Long
Application.ScreenUpdating = False
stFile = Dir(stPath & "\")
While stFile <> ""
If InStr(Right(stFile, 4), "xls") > 0 Then
Set wb = Workbooks.Open(stPath & stFile)
On Error GoTo ErrHandler
If bProtect Then
wb.Protect (stPassword)
Else
wb.Unprotect (stPassword)
End If
wb.Save
wb.Close (True)
lPass = lPass + 1
Debug.Print "PASS: " & stFile & " protection set to " & bProtect
End If
NextFile:
stFile = Dir
Wend
Application.ScreenUpdating = True
MsgBox "Changing protection to " & bProtect & " in" & vbCr & stPath & vbCr & vbCr & lPass & _
" files succeeded" & vbCr & lFail & " files failed", vbInformation + vbOKOnly, "File Protection"
Exit Sub
ErrHandler:
lFail = lFail + 1
Debug.Print "FAIL: " & stFile & " caused an error: " & Err.Number & " - " & Err.Description
wb.Close (False)
Err.Clear
Resume NextFile
End Sub
The neatest way to collect the parameters need would be via userform - see attached file for an example of how it may work.
Bookmarks