Forum Discussion

benk51987's avatar
benk51987
Copper Contributor
Mar 05, 2020

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_Eekelen's avatar
    Riny_van_Eekelen
    Platinum 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)

    • benk51987's avatar
      benk51987
      Copper 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!

Resources