Enabling and Disabling Events

You can use the Application object's EnableEvents property to enable or disable events. If you are writing a VBA procedure and don't want a particular event to occur, set the EnableEvents property to False. For example, to avoid triggering the Workbook_BeforeClose event while running the EnterData procedure (see below), set the EnableEvents property to False before calling the Workbook object's Close method. Set EnableEvents back to True before your procedure finishes running to ensure that events are enabled.

1. Open a new workbook and save it as DisableEvents.xls.

2. Switch to the Visual Basic Editor screen. Double-click ThisWorkbook in the Project Explorer window, and enter the Workbook_BeforeSave event procedure in the Code window that appears.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If MsgBox("Would you like to copy " & vbCrLf _ & "this worksheet to " & vbCrLf _ & "a new workbook?", vbYesNo) = vbYes Then Sheets(ActiveSheet.Name).Copy End If End Sub

3. Choose Insert | Module to add a standard module to the active VBA project, and enter the procedure shown below:

Sub EnterData()

With ActiveSheet.Range("A1:B1") .Font.Color = vbRed .Value = 15 End With

Application.EnableEvents = False ActiveWorkbook.Save Application.EnableEvents = True End Sub

4. Switch to the Microsoft Excel application window, and choose File | Save. The Workbook_BeforeSave event will be triggered at this time. Click Yes in response to the message box. Excel will open a new workbook with the copy of the current worksheet.

5. Activate the DisableEvents workbook, and choose Tools | Macro | Macros. In the dialog box, click EnterData and then Run. Notice that when you run the EnterData procedure, you are not prompted to copy the worksheet before saving. This indicates that the Workbook_Before-Save event is not running.

0 0

Post a comment