Worksheet protection set using ws.protect - but doesnt unprotect using the Menu (Review - Unprotect sheet)

2
votes

I have the following code to protect the sheets of a workbook in Excel 2007

Private Sub password_protectallsheets()
For Each ws In Worksheets
ws.protect Password = "edc1"
Next
End Sub

but when I try to unprotect the sheet using the password through the Excel 2007 Menu (review -> Unprotect sheet), it says the password you have supplied is not correct.

Any help is hugely appreciated.

4
excelvbaprotection

4 Answers

1
votes

The password argument needs to be specified with ":=" - not just "=". This is true for arguments to methods in general:

ws.protect Password := "edc1"

To have the compiler catch these types of errors, always use Option Explicit at the beginning of modules, as JMax has done above. When you do so, the compiler will give you a "Variable not Defined" error when you forget the ":" Option Explicit will save you lots of time with other types of variable declaration errors. To enable it for all new modules check Tools>Options>Edit>Require Variable Declaration in the VBE.

The part I can't figure out is why, in the code without the ":" a password is assigned at all. I would have expected the sheet to be protected, but without a password.

0
votes

Does the Ws.Unprotect really protect the worksheet? - surly it unprotects a worksheet it doesn't set the protection

to set the protection in a macro why not record protecting a worksheet and have a look at the code

0
votes

Try:

Option Explicit

Private Sub password_protectallsheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect "edc1"
    Next
End Sub

without the password key argument.
It worked on my Excel 2007.

Btw, be sure you copy/paste the password or be sure to check if the last character is wether a 1 (one) or a l (lower-case letter L).

0
votes

This ws.protect Password = "edc1" or ws.protect Password := "edc1" No Work! it is necessary to send the password by variable, to fix this bug!

Try this one, it worked for me:

For one sheet

Sub Protect()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("SHEET")
Dim strPassword As String: strPassword = "YourPassword"
ws.Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
        AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False
End Sub

and

Sub UnProtect()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("SHEET")
Dim strPassword As String: strPassword = "YourPassword"
ws.Unprotect Password:=strPassword
End Sub

All Worksheets

Sub ProtectAllSheets()
    Dim ws As Worksheet
    Dim strPassword As String: strPassword = "YourPassword"
    
    For Each ws In Worksheets
    ws.Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
        AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False
    Next ws
    
End Sub

and

Sub UnProtectAllSheets()
    Dim ws As Worksheet
    Dim strPassword As String: strPassword = "YourPassword"
    
    For Each ws In Worksheets
        ws.Unprotect Password:=strPassword
    Next ws
    
End Sub