Microsoft Office Tutorials and References
In Depth Information
CREATE AN EVENT HANDLER MACRO
Tip: You may get to the point where you have many macros spread across
many modules. You can quickly fi nd a macro in the regular Excel interface
by pressing Alt+F8 to bring up an alphabetical list of all the macros. Find the
desired macro and click the Edit button. Excel switches to VBA, opens the
correct module, and scrolls so this module is in view. Unfortunately, this trick
does not work for macros in hidden workbooks, such as the Personal Macro
Workbook.
Summary: You type regular VBA macros into standard modules in the VBA
editor.
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 fi le 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:
Open the workbook in Excel.
Switch to the VBA editor by pressing Alt+F11.
Open the Project Explorer by pressing Ctrl+R.
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.
1.
2.
3.
4.
 
 
Search JabSto ::




Custom Search