Workbook_Open()不适用于Workbook Protection [英] Workbook_Open() does not work with Workbook Protection

问题描述

我正在使用Excel 2016，并在VBA的ThisWorkbook对象中编写了此代码:

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

它不起作用.此处的要点是，这应防止用户接触此工作簿中的Power Query功能.我已将其保存为启用宏的工作簿，同时启用了所有宏和事件，这是唯一打开的工作簿.

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.

我还有以下附加代码:

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

那也不起作用.如果我插入"ThisWorkbook.Protect"，效果很好.代码放入通用模块或工作表对象中并手动运行，但是当我希望该特定excel文件在打开或关闭时自动运行此代码时，它不会执行该操作.

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?

推荐答案

出于某种原因，在受保护的工作簿上运行ThisWorkbook.Protect似乎无法对其进行保护(即使我找不到任何说明它可以做到这一点的文档)，因此试试这个:

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

这篇关于Workbook_Open()不适用于Workbook Protection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！