CODEWITHSUNDEEP
excelvba
Peter Peazy Littlefield
Peter Peazy Littlefield

Reputation: 31

How to auto-protect individual worksheets in Excel using VBA?

I've put this code into a worksheet which should make sure individual worksheets are protected with their individual passwords. It seems to work fine but I'm trying to access the worksheets and all of them are saying incorrect password?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With ThisWorkbook
        .Worksheets("2073 NSW").Protect Password = "2073"
        .Worksheets("2091 NSW").Protect Password = "2091"
        .Worksheets("3105 VIC").Protect Password = "3105"
        .Worksheets("3091 VIC").Protect Password = "3091"
        .Worksheets("4058 QLD").Protect Password = "4058"
        .Worksheets("4091 QLD").Protect Password = "4091"
        .Worksheets("6024 WA").Protect Password = "6024"
        .Worksheets("6091 WA").Protect Password = "6091"
    End With
    Application.EnableAnimations = False
    ThisWorkbook.Save
    Application.EnableEvents = True
End Sub

Upvotes: 0

Views: 108

Answers (1)

Pia Wurtzbach
Pia Wurtzbach

Reputation: 94

The problem is inside the With ThisWorkbook line. It should be Password:="2073" not Password = "2073"
You forgot to use the := which used to assign a value to a certain named argument.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With ThisWorkbook
        .Sheets("2073 NSW").Protect Password:="2073"
        .Sheets("2091 NSW").Protect Password:="2091"
        .Sheets("3105 VIC").Protect Password:="3105"
        .Sheets("3091 VIC").Protect Password:="3091"
        .Sheets("4058 QLD").Protect Password:="4058"
        .Sheets("4091 QLD").Protect Password:="4091"
        .Sheets("6024 WA").Protect Password:="6024"
        .Sheets("6091 WA").Protect Password:="6091"
    End With
    Application.EnableAnimations = False
    ThisWorkbook.Save
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions