Workbook_Open() does not work with Workbook Protection

Question

I am using Excel 2016 and have this code written in the ThisWorkbook object in VBA:

Private Sub Workbook_Open()
ThisWorkbook.Protect (password = "password")
End Sub

It is not working. The point here is that this should prevent users from touching the Power Query functions in this workbook. I have saved it as a Macro-enabled workbook, I have all macros and events enabled and this is the only workbook open.

I also have this additional code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Protect (password = "password")
ThisWorkbook.Save
End Sub

And that is not working either. It works fine if I insert that "ThisWorkbook.Protect" code into a general module or worksheet object and run it manually, but when I want this particular excel file to run this code automatically on open or close, it does not do it.

Any ideas what could be causing this?

Answer 1

For some reason running ThisWorkbook.Protect on a protected workbook seems to unprotect it (even though I couldn't find any documentation that says that it does this) so try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not ThisWorkbook.ProtectStructure Then ThisWorkbook.Protect Password:="password"
    ThisWorkbook.Save
End Sub