Understanding Excel Events

USERFORM EVENTS

Excel associates UserForm events not only with the form but also with each of the controls that exist on the form. You need to place event-handling procedures related to a UserForm in the code module for the UserForm object. The following table lists the available UserForm events.

EVENT

DESCRIPTION

Activate

Excel activates the UserForm.

AddControl

Excel adds a run-time control to the UserForm.

BeforeDragOver

The user performs a drag-and-drop operation.

BeforeDropOrPaste

The user releases the mouse button to paste the data from the drag-and-drop operation.

Click

The user clicks the mouse on a UserForm object. See Chapter 14 for information on capturing the Click event.

DblClick

The user double-clicks the mouse on a UserForm object.

Deactivate

The user deactivates the UserForm.

Error

Excel detects a UserForm control error.

KeyDown

The user presses a key.

KeyPress

The user presses an ANSI key. An ANSI key produces a visible character.

KeyUp

The user releases a key.

MouseDown

The user presses a mouse button.

MouseMove

The user moves a mouse on the UserForm.

MouseUp

The user releases the mouse button.

QueryClose

Excel closes the UserForm.

RemoveControl

Excel removes a control from the UserForm at run-time.

Scroll

The user repositions a Scroll box on a control.

Terminate

Excel terminates the UserForm.

Zoom

The user zooms the UserForm.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

APPLICATION EVENTS

Application events include all events that the Application object recognizes. To access an application event you create a class module to contain your application event-handling procedure code. See the section "Run a Procedure when Excel Creates a

Workbook" for more information on placing event-handling code in a class module.

The following table provides a list of the application-level events that occur within Excel.

EVENT TYPE

DESCRIPTION

Application

An event that occurs for the application, in this case Excel. For example, Excel triggers the NewWorkbook event when it creates a new workbook.

NewWorkbook

Occurs when Excel creates a new workbook. See the section "Run a Procedure when Excel Creates a Workbook"

SheetActivate

Excel activates any sheet in any workbook.

SheetBeforeDoubleClick

Event occurs before the user double-clicks any sheet with the mouse.

SheectBeforeRightClick

Event occurs before the user clicks any sheet with the right mouse button.

SheetCalculate

Excel calculates any worksheet.

SheetChange

Cells on a worksheet change either due to a user or an external link.

SheetFollowHyperlink

A user clicks a hyperlink on a sheet.

SheetPivotTableUpdate

Excel updates a sheet of a Pivot table report.

SheetSelectionChange

The selection changes on any worksheet.

WindowActivate

Excel activates a worksheet window.

WindowDeactivate

Excel deactivates a worksheet window.

WindowResize

The user resizes a worksheet window.

WorkbookActivate

The user activates a workbook.

WorkbookAddInInstall

An add-in installs a workbook.

WorkbookAddInUninstall

An add-in uninstalls a workbook.

WorkbookBeforePrint

Excel prints an open workbook.

WorkbookBeforeSave

Excel saves an open workbook.

WorkbookDeactivate

Excel deactivates a workbook.

WorkbookNewSheet

Excel adds a new sheet to an open workbook.

WorkbookOpen

Excel opens a workbook.

WorkbookPivotTableCloseConnection

Occurs after a Pivot table report closes the data source connection.

WorkbookPivotTableOpenConnection

Occurs after a Pivot table report opens the data source connection.

RUN A PROCEDURE AS A WORKBOOK OPENS

You can create a procedure that runs automatically each time a particular workbook opens. Because this type of procedure only executes once as the workbook opens, it works well for launching custom menus and toolbars, opening other workbooks, determining if specific conditions are met, or displaying welcome messages. The procedure executes when the workbook opens by catching the Open event that the opening workbook triggers.

To create a procedure that executes when a workbook opens, you create a new procedure and add it to the ThisWorkbook object code module for the particular workbook. In fact, all event-handling procedures that you create for monitoring workbook events must reside within the ThisWorkbook object to have Excel execute them automatically. To create a procedure that executes when a workbook opens, you name the procedure Workbook_Open.

Although the procedure resides in the ThisWorkbook object code module, it can access other procedures within the same workbook. Therefore, you can create a Workbook_Open procedure that calls procedures located in other modules.

If you have a procedure that you want to execute whenever Excel opens, you must place the procedure within the ThisWorkbook object for the Personal Macro Workbook, Personal.xls. Because the Personal Macro Workbook always loads as a hidden workbook in Excel, any procedures within this workbook appear to execute as Excel opens. Keep in mind, however, that Excel associates the Personal Macro Workbook with an individual user.

Remember, you can keep a Workbook_Open procedure from executing for a particular workbook by holding down the Shift key as the workbook opens. Because workbooks typically open rather quickly, you need to make sure you press and hold the Shift key as soon as you select the workbook.

RUN A PROCEDURE AS A WORKBOOK OPENS

RUN A PROCEDURE AS A WORKBOOK OPENS

|ThisWorkbook -*■! AcceptLabelsInF False AutoUpdateFreq 0 ChangeHistoryDi 0 ConflictResolutio 1 - xlUser Datel904 False 3isplayDrawingC -4104 - il EnableAutoReco True EnvelopeVisible False

| ThisWorkbook AcceptLabelsInF False AutoUpdateFreq 0 ChangeHistoryDi 0 ConflictResolutio 1 - xlUserResol Datel904 False DisplayDrawingC -4104 -EnableAutoReco True EnvelopeVisible False

'-n On the Projects window, locate the workbook where you want to add the Workbook Open subroutine.

0 Double-click the ThisWorkbook object node under the workbook.

| ThisWorkbook AcceptLabelsInF False AutoUpdateFreq 0 ChangeHistoryDi 0 ConflictResolutio 1 - xlUserResol Datel904 False DisplayDrawingC -4104 -EnableAutoReco True EnvelopeVisible False

|ThisWorkbook -*■! AcceptLabelsInF False AutoUpdateFreq 0 ChangeHistoryDi 0 ConflictResolutio 1 - xlUser Datel904 False 3isplayDrawingC -4104 - il EnableAutoReco True EnvelopeVisible False

'-n On the Projects window, locate the workbook where you want to add the Workbook Open subroutine.

0 Double-click the ThisWorkbook object node under the workbook.

^0 In the Object box, click Q and then click the Workbook option.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

You can use the Open method of the Workbooks collection object to specify a workbook that Excel should open whenever the current workbook opens. For example, if your workbook relies on data values within another workbook, you can open that workbook whenever the current workbook opens. See Chapter 9 for more information on using the Open method to specify the workbook to open.

You can use the Object drop-down list on the Code window to quickly create your Workbook_Open subroutine. The Object drop-down list contains the available objects for which you can create subroutines within the current code module. For example, if you access the ThisWorkbook code module, the only available object is Workbook.

When you select the Workbook object from the Objects dropdown list, the Visual Basic Editor automatically creates a private subroutine called Workbook_Open. This is because the default event for the Workbook object is the Open event. If you view the Procedure drop-down list, you see all the available events for the Workbook object. If you select another event from the list, the Visual Basic Editor creates a new subroutine for that event.

I The Workbook_Open procedure executes the specified VBA code as the workbook opens.

■ The Visual Basic Editor creates a new Private subroutine named Workbook_Open.

Type the VBA code to run when the workbook opens.

E3 Click the Save button (pal) to save the workbook including the new subroutine.

0 Close Excel.

I The Workbook_Open procedure executes the specified VBA code as the workbook opens.

0 0

Post a comment