CODEWITHSUNDEEP
excelvbaautomationpassword-protectionexcel-2016
YariLei
YariLei

Reputation: 3

Workbook_Open() does not work with Workbook Protection

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?

Upvotes: 0

Views: 774

Answers (2)

Chris
Chris

Reputation: 1

In order for automatically-running subroutines to work correctly in Microsoft Excel, they must be contained within a Visual Basic module.

If an Auto_Open, Auto_Close, or other automatically-running subroutine is stored "behind" a worksheet or ThisWorkbook, it may not function correctly when you open or close your workbook, or when you perform an action that should cause the subroutine to run.

MS Topic Discussion - Code "Behind" a Workbook

Upvotes: 0

garbb
garbb

Reputation: 679

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

Upvotes: 1

Related Questions