EnableEvents = False但仍触发事件 [英] EnableEvents = False but still the event is triggered

问题描述

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim txtFileName As String
'1. Check of Save As was used.
   If SaveAsUI = True Then
        Cancel = True

'2. Call up your own dialog box.  Cancel out if user Cancels in the dialog box.
       txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "Action Cancelled", vbOKOnly
            Cancel = True
            Exit Sub
        End If

'3. Save the file.
       Application.EnableEvents = False
       ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
       Application.EnableEvents = True

    End If
End Sub

我使用上面的代码强制用户保存为xlsm。此代码在使用Excel 14.0.6106.5005的PC上正常工作。但是在使用Excel 14.0.6129.5000的PC上，这会产生一个新的"另存为"。输入文件名并单击
中的保存按钮后的对话框原始"另存为XLSM文件"对话。两台PC都在Windows 7上运行。

I use the code above to force users to save as xlsm. This code works fine on a PC with Excel 14.0.6106.5005. But on a PC with Excel 14.0.6129.5000 this results in a new "Save As" dialog after entering a filename and clicking the Save button in the original "Save As XLSM file" dialog. Both PCs run on windows 7.

在我看来，尽管EnableEvents = False设置，仍然会在这台PC上触发SaveAs事件，而不是在另一台PC上触发

It seems to me that despite the EnableEvents = False setting, the SaveAs event is still triggered on this one PC, not on the other

任何人都知道为什么这段代码在一个Excel版本上工作而不是在另一个Excel版本上工作？

Anyone an idea why this code works as intended on one Excel version and not on another Excel version?

推荐答案

以下修改过的代码应该允许您测试您的假设，因为MsgBox会告诉您何时调用例程。

The following modified code should allow you test your assumptions because the MsgBox will tell you when the routine is called.

另请参阅测试EnableEvents状态，以便在系统忽略它时强制退出sub。

See also the testing of the EnableEvents state to force an exit from the sub if the system is ignoring it.

我无法模拟您的问题，但我怀疑在输入文件名后单击"保存"时，文件可能会在此时保存，并且在该行代码之前尚未禁用事件。我已经包含了代码来确定
工作簿是否已保存。请注意，在使用"另存为"之前，您需要在工作簿中进行虚拟更改以确定这是否是一个问题，因为如果没有更改，它将始终为"已保存"。

I can't emulate your problem but I am suspecting that when Save is clicked after entering the file name that the file might be saving at that time and events have not been disabled prior to that line of code. I have included code to determine if the workbook has been saved. Note you will need to make a dummy change in the workbook prior to using Save As to determine if this is a problem because without a change it will always be "Saved."

Private Sub Workbook_BeforeSave（ByVal SaveAsUI As Boolean，Cancel As Boolean）

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    MsgBox"Workbook_BeforeSave called" '< - ***测试子被叫为
   

   如果Application.EnableEvents = False那么    '< - *** Test EnableEvents条件

        MsgBox" EnableEvents为False"          '< --- ***有关开发期间的信息

        Application.EnableEvents = True

       退出子

   结束如果是
   

    Dim txtFileName As String

       

    "1。检查使用的另存为。

   如果SaveAsUI = True则为
       取消=真

    MsgBox "Workbook_BeforeSave called" '<--*** Test if sub called
   
    If Application.EnableEvents = False Then    '<--*** Test EnableEvents condition
        MsgBox "EnableEvents is False"          '<---*** For information during development
        Application.EnableEvents = True
        Exit Sub
    End If
   
    Dim txtFileName As String
       
    '1. Check of Save As was used.
    If SaveAsUI = True Then
        Cancel = True

        "2。调出你自己的对话框。 如果用户在对话框中取消，则取消。

        txtFileName = Application.GetSaveAsFilename（，"Excel启用宏的工作簿（* .xlsm），*。xlsm"，"和另存为XLSM文件"）

     &NBSP;&NBSP;&NBSP;如果txtFileName =" False"然后是
            MsgBox"取消操作"，vbOKOnly

           取消= True   '< --- ***不应该被要求。已经设置为True以上为¥b $ b           退出Sub¥
       结束如果

       

        '使用以下方法测试工作簿是否保存在上一个对话框中。

       如果ThisWorkbook.Saved = True则为
            MsgBox"工作簿已保存。"&
       结束如果

        '2. Call up your own dialog box.  Cancel out if user Cancels in the dialog box.
        txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "Action Cancelled", vbOKOnly
            Cancel = True   '<---*** Should not be required. Already set to True above
            Exit Sub
        End If
       
        'Use following to test if the workbook saved in previous dialog.
        If ThisWorkbook.Saved = True Then
            MsgBox "Workbook already saved."
        End If

        "3。保存文件。

        On Go GoTo ReEnableEvents    '< --- ***确保事件在发生错误后重新开启。
        Application.EnableEvents = False

        ThisWorkbook.SaveAs Filename：= txtFileName，FileFormat：= xlOpenXMLWorkbookMacroEnabled

   结束如果

       

ReEnableEvents：

        Application.EnableEvents = True

        '3. Save the file.
        On Error GoTo ReEnableEvents    '<---*** Ensure Events get turned back on after error
        Application.EnableEvents = False
        ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    End If
       
ReEnableEvents:
        Application.EnableEvents = True

End Sub

这篇关于EnableEvents = False但仍触发事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！