Excel VBA：另存为触发组合框中的更改事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3568403/
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
Excel VBA: Save As triggers Change event in ComboBox
提问by MarkJ
I have an Excel workbook containing some ComboBox controls placed directly on the sheets. These are standard combo boxes from the Forms toolbar.
我有一个 Excel 工作簿，其中包含一些直接放置在工作表上的 ComboBox 控件。这些是表单工具栏中的标准组合框。
When the user uses "Save As" to save the workbook with a different name, this triggers the Change event on all the combo boxes, including ones on sheets that aren't active. This seems unreasonable as the selection hasn't actually changed. This causes various undesirable behaviour because of the code in the event handlers. The event isn't triggered on a simple "Save".
当用户使用“另存为”以不同的名称保存工作簿时，这会触发所有组合框上的 Change 事件，包括非活动工作表上的组合框。这似乎不合理，因为选择实际上并没有改变。由于事件处理程序中的代码，这会导致各种不良行为。该事件不会在简单的“保存”时触发。
Google suggeststhis is a known problem in Excel. There are rumoursthat it's caused by using a named range as the ListFillRange for the combo box, which I have done, although it's not a volatile name. I'm looking for a way to prevent this happening with minimal changes to the code and the spreadsheet. Does anyone have a proven solution?
谷歌建议这是 Excel 中的一个已知问题。有传言说它是由使用命名范围作为组合框的 ListFillRange 引起的，我已经这样做了，尽管它不是一个易变的名称。我正在寻找一种方法来防止这种情况发生，同时对代码和电子表格进行最少的更改。有人有经过验证的解决方案吗？
回答by adamleerich
I did the following in a new workbook with only one sheet, Sheet1, and it seemed to work to diable events before save and then reenable them after. It should bypass the problem you see by mimicing an AfterSaveevent. This is my event code on Sheet1 (could be OLEObject code, too)
我在只有一张工作表 Sheet1 的新工作簿中执行了以下操作，它似乎可以在保存之前禁用事件，然后在之后重新启用它们。它应该通过模拟AfterSave事件来绕过您看到的问题。这是我在 Sheet1 上的事件代码（也可能是 OLEObject 代码）
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address & ": " & Target.Value
End Sub
This is my ThisWorkbook code
这是我的 ThisWorkbook 代码
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' To see the change code work before disabling
' Should show a message box
Sheet1.Range("A1") = "Before After Events Off"
Application.EnableEvents = False
Application.OnTime Now, "ThisWorkbook.Workbook_AfterSave"
' This time it will not show a message box
' You will never see this one . . .
Sheet1.Range("A1") = "After After Events Off"
End Sub
Private Sub Workbook_AfterSave()
Application.EnableEvents = True
End Sub
The .OnTimemethod throws the AfterSave "event" onto the execution queue. It works!
该.OnTime方法将 AfterSave “事件”抛出到执行队列中。有用！
回答by mwolfe02
You could set a flag in the Workbook's BeforeSave event and then check that flag before processing a change event in each of the combo boxes. There does not seem to be an AfterSave event, so you would need to clear the flag after checking it within the combo box change events. The flag would need to be more than a simple boolean since it could not be turned off until all combo box change events were processed. Here's some sample code:
您可以在工作簿的 BeforeSave 事件中设置一个标志，然后在处理每个组合框中的更改事件之前检查该标志。似乎没有 AfterSave 事件，因此您需要在组合框更改事件中选中该标志后清除该标志。该标志需要的不仅仅是一个简单的布尔值，因为在处理完所有组合框更改事件之前无法将其关闭。这是一些示例代码：
Public ComboBoxChangeCounter As Integer
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Const NumOfComboBoxChangeEvents As Integer = 5
ComboBoxChangeCounter = NumOfComboBoxChangeEvents
End Sub
Function JustSaved() As Boolean
If ComboBoxChangeCounter > 0 Then
ComboBoxChangeCounter = ComboBoxChangeCounter - 1
JustSaved = True
End If
End Function
Private Sub Combo1_Change()
If JustSaved Then Exit Sub
'Your existing code '
' ... '
' ... '
' ... '
End Sub
I set the number of combo box change events as a constant, but there may be some way for you to determine that number programmatically. This workaround does require adding code to every combo box change event, but it should be easy as all you need to do is copy and paste the line If JustSaved Then Exit Subat the beginning of each event.
我将组合框更改事件的数量设置为常量，但您可能可以通过某种方式以编程方式确定该数量。此解决方法确实需要向每个组合框更改事件添加代码，但它应该很容易，因为您需要做的只是复制并粘贴If JustSaved Then Exit Sub每个事件开头的行。
This workaround assumes that the Workbook BeforeSave event will get called prior to the combo box change events. I don't know for a fact if that's the case.
此变通方法假定 Workbook BeforeSave 事件将在组合框更改事件之前被调用。我不知道事实是否是这样。
