MrExcel.com - Your One Stop for Excel Tips & Solutions

Create An Event Handler Macro

May 17, 2021 - by

Create An Event Handler Macro

Challenge: While typical macros are entered in modules, a few special macros, called event handler macros, are run automatically in response to an event that happens in Excel.

Background: Some common event handler macros are:

  • Workbook_Open: This macro runs when a workbook is opened.
  • Workbook_BeforeClose: This macro runs after someone tries to close a workbook but before the person is asked whether to save changes.
  • Workbook_BeforePrint: This macro runs when someone issues a print command but before the job is sent to the printer. This macro allows you to adjust something in the workbook, such as adding the current file location and the user name in the footer of the worksheet.
  • Worksheet_Change: This amazing macro runs every time someone enters a value in any cell in the worksheet.
  • Worksheet_Activate: This is sort of a a Workbook_Open macro but for an individual worksheet. Maybe you want certain menu items to appear only when someone is on a particular worksheet.
  • Worksheet_SelectionChange: This macro runs every time someone moves to a new cell in the worksheet.

Event handler macros are powerful, but they do not work correctly unless they are entered properly in the VBA editor. The VBA code is not entered in a traditional module but is entered in the code pane attached to the worksheet or to the ThisWorkbook object.

Solution: Say that you have found some code for the BeforePrint event. This is a workbook-level event, so it needs to go on the code pane for the workbook. Follow these steps:

  1. Open the workbook in Excel.
  2. Switch to the VBA editor by pressing Alt+F11.
  3. Open the Project Explorer by pressing Ctrl+R.
  4. Find your workbook in the Project Explorer list. If necessary, click the + sign to the left of the workbook name to expand the tree view for the workbook.
  5. If necessary, click the + sign to the left of Microsoft Excel Objects to expand the view of worksheets and the workbook. You now see an entry for each worksheet in the workbook and an entry for ThisWorkbook.
  6. Double-click ThisWorkbook. Alternatively, right-click ThisWorkbook and choose View Code (Figure 123).
    Figure 123. You need to access the code pane for the ThisWorkbook object.
    Figure 123. You need to access the code pane for the ThisWorkbook object.
  7. If you are copying a macro from the web, paste it to the code window now. If you are typing the macro yourself, follow steps 8 and 9.
  8. From the left dropdown at the top of the code window, choose Workbook. By default, Excel types the start of a Workbook Open macro in the code window. You can delete it later, if needed.
  9. Open the right dropdown at the top of the code window. These are all the workbook-level events that can have a macro associated with them. Choose BeforePrint from the dropdown. Excel types the start of the Workbook_BeforePrint macro in the code window.

The process is similar if you want to create a worksheet-level event handler. In that case, you follow the steps above, but in step 6, you double-click the worksheet name in the Project Explorer, and in step 8, you choose Worksheet from the left dropdown. Excel automatically creates the start of the Worksheet SelectionChange macro.

Summary: You need to type event handler macros in special code panes attached to the worksheet or workbook.

Title Photo: Noiseporn on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.