vba 当我打开工作簿时，Workbook_Open sub 不会运行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11369762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Workbook_Open sub won't run when I open the workbook?
提问by TheTreeMan
This program is supposed to create a button that the user can press to activate a different sub. From my searches online, it seems that the sub below should activate when opening up the workbook, but it's not?
这个程序应该创建一个按钮，用户可以按下它来激活不同的子程序。从我在网上的搜索来看，打开工作簿时似乎应该激活下面的子项，但事实并非如此？
What am I doing wrong?
我究竟做错了什么？
Option Explicit
Private Sub Workbook_Open()
Dim btn As Button
Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Range("B2:C2")
Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
With btn
.Caption = "To begin the program, please click this button"
.AutoSize = True
.OnAction = "TableCreation1"
End With
End With
End Sub
回答by danielpiestrak
Make sure your Private Sub Workbook_Open()subroutine is pasted inside of the This Workbookobject and not in a Module, Form, or Sheet object.
确保您的Private Sub Workbook_Open()子例程粘贴在This Workbook对象中，而不是在 Module、Form 或 Sheet 对象中。
回答by DrMarbuse
Interesting. In 2009 a conflict with conditional formatting of the sheet to open is described, as in vbforum post.
有趣的。在 2009 年，描述了与要打开的工作表的条件格式的冲突，如 vbforum post。
It seems that this bug still exists in excel and prevents the workbook_openevent from being fired.
I have a workbook (old XLS-binary format) that simply does not fire the event in Excel 2003 and 2007 but does in 2013. I deleted all conditional formatting from the first worksheet but could still not get the workbook_openprocedure to run in elder Excel-Versions.
似乎这个错误仍然存在于excel中，并阻止了该workbook_open事件被触发。我有一个工作簿（旧的 XLS 二进制格式），它根本不会在 Excel 2003 和 2007 中触发事件，但会在 2013 年触发。我从第一个工作表中删除了所有条件格式，但仍然无法让workbook_open程序在旧Excel中运行-版本。
A Workaround, I use in distributed workbooks is to use a local variable and a second event in the workbook as follows:
我在分布式工作簿中使用的解决方法是在工作簿中使用局部变量和第二个事件，如下所示：
''
' private variable
Private wbOpenEventRun as Boolean
''
' procedure to be called by excel when workbook opens
Private Sub Workbook_Open()
wbOpenEventRun = true
' perform tasks
End Sub
''
' the selection change event fires usually.
' performance is not reduced
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not wbOpenEventRun Then Workbook_Open
' perform tasks in reaction of selection change events, if required
End Sub
回答by Shawn
I know this post has been dormant for a while, but I just struggled for hours to solve this problem. It's the oddest thing, but I finally noticed that one of my worksheets was in "Page View"... and as soon as I put it into "Normal" my Workbook_Open() function started working as normal again. VERY STRANGE - definitely a Excel bug... just glad I finally solved it... Hope it helps someone...
我知道这篇文章已经沉寂了一段时间，但我只是努力了几个小时来解决这个问题。这是最奇怪的事情，但我终于注意到我的一个工作表在“页面视图”中......一旦我将它放入“正常”，我的 Workbook_Open() 函数又开始正常工作了。非常奇怪 - 绝对是一个 Excel 错误......很高兴我终于解决了它......希望它可以帮助某人......
回答by Nazim
The solution I found was running the below code and then the "Open" event worked.
我找到的解决方案是运行以下代码，然后“打开”事件起作用。
Sub EventRestore()
Application.EnableEvents = True
End Sub
回答by Andreas Dietrich
Similar to Nazim's event enable via code solutionI found out the following fix:
与通过代码解决方案启用 Nazim 的事件类似，我发现了以下修复：
the fix
修复
closing all excel workbooks and VBA windows and reopening the one with Workbook_Open()solved it.
(likely due to the event enabling similar to Nazim's solution linked above).
关闭所有 excel 工作簿和 VBA 窗口并重新打开一个Workbook_Open()解决它。
（可能是由于事件启用类似于上面链接的Nazim 的解决方案）。
problem experience
问题经验
As I was debugging and aborted execution, I disabled the events in the running code before abort. I did not notice this circumstance at first and even after close/reopen of my workbook it did not work...
在调试和中止执行时，我在中止之前禁用了正在运行的代码中的事件。我一开始没有注意到这种情况，即使在关闭/重新打开我的工作簿后它也不起作用......
probable cause
可能的原因
...The other open excel workbooks or some global instance somehow "remembered" that the events of the reopened workbook had been turned off.
...其他打开的 excel 工作簿或某个全局实例以某种方式“记住”了重新打开的工作簿的事件已被关闭。
回答by CaptainFun
My solution was kind of obscure, and I don't even remember now why it occurred to me as a potential solution. The file I created in which the Workbook_Open() macro would not run, I originally created through the following steps:
我的解决方案有点晦涩，现在我什至不记得为什么我认为它是一个潜在的解决方案。我创建的 Workbook_Open() 宏无法运行的文件，我最初是通过以下步骤创建的：
- Right clicked in the relevant folder, clicked New, clicked Microsoft Excel Worksheet.
- Named the file "Workbook_1".
- Opened Workbook_1.
- File, Save As, changed file type to Excel Macro-Enabled Workbook (*.xlsm), Save.
- Wrote VBA code including a Workbook_Open()nested in ThisWorkbook.
- 右键单击相关文件夹，单击New，单击Microsoft Excel Worksheet。
- 将文件命名为“ Workbook_1”。
- 打开Workbook_1。
- 文件，另存为，将文件类型更改为Excel 启用宏的工作簿 (*.xlsm)，保存。
- 编写了包含嵌套在ThisWorkbook 中的Workbook_Open() 的VBA 代码。
For whatever reason, I wondered if my problem had anything to do with the fact that the file "started as" a standard .xlsx. So I simply:
无论出于何种原因，我想知道我的问题是否与文件“开始为”标准.xlsx的事实有关。所以我简单地：
- Opened a new blank Workbook within Excel (Book1).
- File, Save As, input Workbook_2, changed filed type to Excel Macro-Enabled Workbook (*.xlsm), Save.
- Copied VBA code and structure from the original Workbook_1.
- 在 Excel ( Book1) 中打开了一个新的空白工作簿。
- 文件，另存为，输入Workbook_2，将文件类型更改为Excel Macro-Enabled Workbook (*.xlsm)，保存。
- 从原始Workbook_1复制 VBA 代码和结构。
Unlike the original Workbook_1, the new Workbook_2successfully ran the Workbook_Open()sub on open. Maybe a potential cause of this problem is related to the file's type history (i.e. if it was at some point a type that cannot run macros). Or maybe simply trying again with a new file is what solved this problem for me. Either way, this may work for anyone for which other solutions did not.
与原始Workbook_1不同，新Workbook_2在打开时成功运行Workbook_Open()子。也许这个问题的潜在原因与文件的类型历史有关（即，如果它在某个时候是不能运行宏的类型）。或者也许只是用一个新文件再试一次就是为我解决这个问题的方法。无论哪种方式，这可能适用于其他解决方案不适用的任何人。
