Forum Discussion
Password protected sheets can still be unprotected without password
I created a macro to password protect all sheets in my workbook. However, after doing so, you can still right-click on any sheet and unprotect it without being prompted for the password. How do I fix this issue?
I used the following code in VBA:
Sub protect_all_sheets()
top:
pass = InputBox("password?")
repass = InputBox("verify password")
If Not (pass = repass) Then
MsgBox "incorrect password"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
s.EnableSelection = xlUnlockedCells
s.EnableSelection = xlLockedCells
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets when running this Macro."
End Sub
2 Replies
- Riny_van_EekelenPlatinum Contributor
benk51987 Ran your code and it seems to do what you intend. Alls sheet becomes locked and can only be unlocked by the password entered.
Tested on a Mac (Catalina, Office365)
- benk51987Copper Contributor
Riny_van_Eekelenthank you for your response. It's weird that mine is not working. I wonder if the spreadsheet has some other code in it somewhere that is causing a conflict? I don't know - I'm at a loss!