excelvbapassword-protectionauto-lock

Set password when closing

I'm trying to lock all sheets with a password when closing a workbook, but allow filtering and searching on tables.

I managed to gather the following which works other than setting the password.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Update by Extendoffice 2018/1/24
Dim xSheet As Worksheet
Dim xPsw As String
xPsw = "***"
For Each xSheet In Worksheets
    xSheet.Protect xPsw
Next
If ActiveSheet.Protection.AllowFiltering = False Then
    ActiveSheet.Protect AllowFiltering:=True
End If
End Sub

*** = password
The above auto locks worksheets and allows filtering as needed but doesn't set the password.

Excel for office 365, win10 enterprise.

Solution

  • i think its bugged because you use protect method twice. try below code

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Update by Extendoffice 2018/1/24
Dim wb As Workbook: Set wb = ThisWorkbook
Dim xSheet As Worksheet
Dim xPsw As String
xPsw = "testpw"
For Each xSheet In wb.Worksheets
   xSheet.Protect xPsw, AllowFiltering:=True
Next
End Sub