Microsoft KB Archive/211626

Knowledge Base

XL2000: EnableEvents Property Does Not Work in Automation Server

Article ID: 211626

Article Last Modified on 11/23/2006

---

APPLIES TO

• Microsoft Excel 2000 Standard Edition

---

This article was previously published under Q211626

SYMPTOMS

When Microsoft Excel 2000 is used as an Automation server during an Automation session, the EnableEvents property may not work as expected. Attempts by the client application to set this property to False through code in the client application are ignored.

CAUSE

This behavior can occur because, during Automation, each line of code that is sent to Excel to be run from an Automation client is treated as a separate Excel macro. The EnableEvents property is turned off (set to False) only for that one line of code, and is automatically turned back on (set to True) for the next line of code that is sent to Excel. Therefore, using the EnableEvents property in this context is not an effective strategy.

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

To set the EnableEvents property to False during an Automation session, use the Run method from the Automation client to run an Excel macro that sets the property to False.

The following example demonstrates how to disable events in an Automation session that controls Excel.

Creating the Excel Macro

To create the macro, follow these steps:

1. Start Microsoft Excel, close all open workbooks, and then open a new workbook.
2. Start the Visual Basic Editor (ALT+F11).

3. On the Insert menu, click Module. In the new module, enter the following code:

   Sub Disable_Events()
      Application.EnableEvents = False
   End Sub
                       

4. In the Project Explorer, double-click ThisWorkbook.
   
   This step displays the code module for the workbook.

5. Enter the following code for the SheetDeactivate event of the workbook:

   Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
      MsgBox Sh.Name
   End Sub
                       

6. Quit the Visual Basic Editor and return to Microsoft Excel.
7. Select the sheet tab for any sheet other than the active sheet.
   
   A message box displays the name of the sheet that was deactivated.
8. Close and save this workbook as Test.xls on the root directory of drive C.
9. Quit Microsoft Excel.

Creating the Automation Macro

To create this macro, follow these steps:

1. Start Microsoft Word 2000, close all open documents, and then open a new document.
2. Start the Visual Basic Editor.

3. Insert a new module in the project and enter the following code in the module:

   Sub Automation_Example()
   
   Dim xlobj As Object
   Set xlobj = CreateObject("excel.application")
   
      xlobj.Visible = True
      xlobj.EnableEvents = False
      xlobj.Workbooks.Open FileName:="c:\Test.xls"
      'xlobj.Run "Disable_Events"
   
   Set xlobj = Nothing
   
   End Sub
                       

4. Run the Automation_Example macro.
   
   Excel is started and the Test.xls workbook is opened.
5. Select any sheet tab other than the active sheet.
   
   The message box is displayed because events were not disabled by EnableEvents property.
6. Quit Excel.

7. Change the Automation_Example macro to the following:

   Sub Automation_Example()
   
   Dim xlobj As Object
   Set xlobj = CreateObject("excel.application")
   
      xlobj.Visible = True
      'xlobj.EnableEvents = False
      xlobj.Workbooks.Open FileName:="c:\Test.xls"
      xlobj.Run "Disable_Events"
   
   Set xlobj = Nothing
   
   End Sub
                       

8. Run the Automation_Example macro.
9. Select any sheet tab other than the active sheet.
   
   The message box is not displayed because events were disabled by the Disable_Events macro in Test.xls.

NOTE: If the Test.xls workbook had contained a Workbook_Open event, it would not have been disabled because the Disable_Events macro in the workbook would have run after the workbook was opened. A workaround to this scenario is to use an Auto_Open macro in the workbook instead of a Workbook_Open event macro. Because Auto_Open macros are not automatically run when a workbook is opened by running a procedure, the macro does not run in this scenario.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

REFERENCES

For more information about the EnableEvents property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type enableevents property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

NOTE: If the Assistant is hidden, click the Office Assistant button on the standard toolbar.

For additional information about the Office Assistant, click the article number below to view the article in the Microsoft Knowledge Base:

231955 OFF2000: Office Assistant Not Answering Visual Basic Questions

Additional query words: ole XL2000

Keywords: kbbug kbpending KB211626

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.