This Workbook Codes dont work

[Challis]

Hi,
I have the following code saved under the "This Workbook" object

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  Dim wsRegister As Worksheet
  Set wsRegister = ThisWorkbook.Sheets(Register)
  wsRegister.Unprotect Password:="BWI2018"
If wsRegister.FilterMode = True Then wsRegister.ShowAllData
wsRegister.Protect Password:="BWI2018", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
Application.GoTo Reference:=Range("a1"), Scroll:=True
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wsRegister As Worksheet
  Set wsRegister = ThisWorkbook.Sheets(Register)
  wsRegister.Unprotect Password:="BWI2018"
If wsRegister.FilterMode = True Then wsRegister.ShowAllData
wsRegister.Protect Password:="BWI2018", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
Application.GoTo Reference:=Range("a1"), Scroll:=True
End Sub


Private Sub Workbook_Open()
Dim wsRegister As Worksheet
  Set wsRegister = ThisWorkbook.Sheets(Register)
  wsRegister.Protect Password:="BWI2018"
  Application.GoTo Reference:=Range("a1"), Scroll:=True
End Sub

I also have a macro button within the spreadsheet which runs the following code to allow users to manually edit a locked sheet

Sub Manual_Entry_or_Edit()
    Dim Ans As String
        Ans = MsgBox("Editting the register requires administrator rights. Do you wish to proceed?", vbYesNo, "Confirm")
    If Ans = vbYes Then
    On Error GoTo ErrHandl
ActiveSheet.Unprotect
End If
Exit Sub
ErrHandl:
MsgBox Err.Description
End Sub

My problem is, when users select the manually edit macro button, make their changes, then save/exit the sheet, the codes in "This workbook" dont work. So when the next user opens the sheet, it remains unprotected.
Can someone help me solve this issue.

 

[yky]

Do these code even work? Looks to me "Register" is not declared. I copied the code, saved to ThisWorkbook, and tried to save the workbook. I immediately got an error message. I don't know how your users could save the file. I can't.

Set wsRegister = ThisWorkbook.Sheets(Register)

 

[mumps]

Since you are protecting the sheet when you open the workbook, you don't really need the Workbook_AfterSave and Workbook_BeforeClose macros. All you would need is this one:

Private Sub Workbook_Open()
    Sheets("Register").Unprotect Password:="BWI2018"
    If Sheets("Register").AutoFilterMode = True Then Sheets("Register").AutoFilterMode = False
    Sheets("Register").Protect Password:="BWI2018"
End Sub

You would also need to include the password in this macro:

Sub Manual_Entry_or_Edit()
    If MsgBox("Editting the register requires administrator rights. Do you wish to proceed?", vbYesNo, "Confirm") = vbYes Then
        On Error GoTo ErrHandl
        ActiveSheet.Unprotect Password:="BWI2018"
    End If
    Exit Sub
ErrHandl:
    MsgBox Err.Description
End Sub

 

[Challis]

Thanks Mumps,
I dont want to include the password in the 2nd Macro, as I want the user to have to enter the password to modify.
Problem still persists however, when users selects the Manual entry button, it unlocks the register. If they make their changes, save and close, the next time the register re-opens it remains unlocked. Ie the Private Sub Workbook_Open() function does't work.
I cant understand why.

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.