我在一个只有一个工作表Sheet1的新工作簿中进行了以下操作,似乎在保存之前禁用事件,然后在保存后重新启用它们可以解决问题。通过模拟AfterSave
事件,应该能够避免你看到的问题。这是我的Sheet1事件代码(也可以是OLEObject代码)。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address & ": " & Target.Value
End Sub
这是我的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
.OnTime
方法会将 AfterSave "事件" 抛到执行队列中。它有效!您可以在工作簿的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
If JustSaved Then Exit Sub
到每个事件的开头。