ActiveX controls aren’t the only objects in Excel that can have events. Worksheets and workbooks have events, too. Each of these objects has different events that it can respond to.
One event you can respond to from a workbook is when the user changes the selection. You can create an event handler that runs every time the selection changes.
In the Visual Basic editor, activate the Sheet1 code window. (In the Project window, double-click Sheet1.)
From the Objects list, at the top left of the code window, select Worksheet.
A new procedure appears with the name Worksheet_SelectionChange. This event happens whenever you change the selection on the worksheet. It doesn’t matter whether you click in a cell or use the arrow keys to move around; the event happens either way.
Just to see what events are available for a worksheet, click the Procedure arrow, at the upper right of the code window. The list shows the nine events that a work-sheet can respond to. SelectionChange is the default event for a worksheet, just as Click is the default event for a command button.
Press Esc to close the list of events, and enter these statements as the body of the Worksheet_SelectionChange procedure:
If ActiveCell.Interior.Color = rgbLightBlue Then Selection.Interior.Color = rgbLightGreen Else Selection.Interior.Color = rgbLightBlue End If
The procedure now changes all the newly selected cell to light blue unless the new active cell already happens to already be light blue.
Activate Sheet1 in Excel, and click in several different cells. Press arrow keys to move between cells. Drag a selection rectangle through several cells. The cell colors change each time you change which cells are selected.
Now activate Sheet2 and select a cell.
Nothing happens. The Worksheet_SelectionChange event handler is active only for the associated worksheet.
When you create an event handler for the Sheet1 SelectionChange event, that handler applies only to that worksheet. If you activate Sheet2 and change the selection, nothing happens. Worksheet event handlers respond to events only on their own worksheet. To handle an event on any worksheet, you must use a workbook-level event handler.
In the Visual Basic editor, activate the Project window and double-click the ThisWorkbook item.
From the Object list, select Workbook.
A new procedure appears with the name Workbook_Open. Open is the default event for a workbook. This is the event you’d use if you wanted to run a procedure every time you open the workbook.
Click the Procedures list to see the events available for a workbook. Scroll down to see the ones that start with the letter S.
A workbook can respond to any of 29 different events. It just so happens that nine of the events begin with Sheet. And these nine workbook Sheet events just happen to correspond to the nine events for a worksheet, except that they apply to any worksheet in the workbook, even worksheets that don’t exist yet.
Select the SheetSelectionChange event.
This creates a new Workbook_SheetSelectionChange procedure.
Delete the Workbook_Open procedure. You won’t need this one.
Type Selection.Interior.Color = rgbLightSalmon as the body of the new procedure.
Switch to Excel, activate Sheet2, and click in various cells. The cells change to light salmon.
Activate Sheet1, and click in various cells.
The cells change to light salmon on this sheet as well. What happened to the SelectionChange event handler procedure for Sheet1? It might seem that the event handler for the workbook replaces the one for an individual sheet, but that’s not quite true. In fact, they both ran; the workbook one just ran last. The property for the interior color of the cell changed to light blue (or light green) and then quickly changed to light salmon. You didn’t see the intermediate color because Windows doesn’t refresh the screen until the macro finishes. So all you ever see is the final color. (If you don’t believe me, put breakpoints in both the workbook procedure and the worksheet procedure and then click a cell on Sheet1.)
In summary, you can create event handler procedures for any of nine different events that take place on a worksheet. You can put those event handler procedures either at the worksheet level or at the workbook level. If the procedure is at the workbook level, it handles events for all worksheets, regardless of whether a worksheet has an event handler of its own.
It might seem strange that a worksheet event handler wouldn’t override a workbook event handler for the same event. In fact, having the worksheet event occur first gives you a great deal of control over how to take advantage of events.
If you want both event handlers to run, you don’t have to do anything. For example, the workbook handler might set the border of the selection while the worksheet handler sets the interior color. But if you do want the worksheet event handler to suppress the workbook event handler, you can set it up to do that. You just make the worksheet event handler tell the workbook event handler to do nothing. The way you do that is by creating a very simple custom property at the workbook level.
Double-click ThisWorkbook in the Project window. At the top of the code window, enter this statement above the event handler procedure:
Public ProcessingEvent As Boolean
This declares ProcessingEvent as a public variable in ThisWorkbook. When you declare a variable above all the procedures in a module, the variable becomes visible to all the procedures in that one module and is called a module-level variable. If you use the word Public to declare a module-level variable, that variable is then visible to any procedure in the entire workbook. A public variable is essentially a simple property. Declaring the variable as Boolean means that it can be only True or False. If you don’t assign something to it, it will be False.
Change the body of the Workbook_SheetSelectionChange event handler to this:
If ProcessingEvent = True Then ProcessingEvent = False Else Selection.Interior.Color = rgbLightSalmon End If
The event handler will now change the color of the selection only if the ProcessingEvent variable is not True. If ProcessingEvent is True, the event handler changes the variable back to False. (If you didn’t change ProcessingEvent back to False, suppressing the event handler once would disable this event handler until you close the workbook.)
Double-click Sheet1 in the Project window. In the Worksheet_SelectionChange event handler, type ThisWorkbook.ProcessingEvent = True just before the End Sub statement.
Notice that the new ProcessingEvent property is in the list of members. It even has a standard Property icon next to it. With this statement, the worksheet event handler tells the workbook event handler that it’s in the middle of processing the event itself. The code in the workbook event handler uses that information to decide whether to do anything or not.
Activate Sheet1 in Excel, and change the selection.
If you set breakpoints in any of the procedures, you may want to remove them before you continue.
The selection should change to light green or light blue. The worksheet event handler is suppressing the workbook event handler.
Activate Sheet3, and change the selection.
The selection should change to light salmon. The workbook event handler still functions properly as long as it’s not suppressed by the worksheet.
Creating a public variable inside ThisWorkbook creates a very simple custom property that allows you to suppress the workbook event handler. You now have total control over which event handlers function at which time. You can have an event handler run only at the worksheet level, only at the workbook level, or at both levels. You are the master.
Some events are made to be canceled. For example, Excel displays a shortcut menu and formatting toolbar as popups when you right-click a worksheet.
What if you want to prevent these from appearing? You can create an event handler procedure that cancels that event.
Events that can be canceled all have the word Before in front of the event name. A worksheet has a BeforeDoubleClick event and a BeforeRightClick event. A workbook has corresponding SheetBeforeDoubleClick and SheetBeforeRightClick events, and also BeforeClose, BeforeSave, and BeforePrint events. Each event procedure that can be canceled has a Cancel argument. Cancelling an event suppresses the normal event handler that Excel would execute for that event. To cancel an event, assign True to the Cancel argument in your event handler procedure.
In the Sheet1 code window, select Worksheet from the Object list, and BeforeRightClick from the Procedures list.
In the event handler procedure that appears, enter the statement Cancel = True as the body.
Activate Excel, and select Sheet1. Try right-clicking a cell.
The color changes because of the SelectionChange procedure, but the normal pop-up windows don’t appear. Your custom event handler executed before the built-in handler and prevented that built-in handler from executing.
Select Sheet2, and try right-clicking a cell.
The color changes, but the pop-up windows also appear.
Press Esc to close the pop-up windows. Then save and close the Chapter09 workbook.
Toolbars and menus can be linked to macros. Command buttons, worksheets, and workbooks can be linked to event handlers. All these tools allow you to create applications that are easy for anyone to use.
CLOSE the Chapter09.xlsm workbook.
In previous editions of Excel, it was possible to use VBA macros to make changes to menus and toolbars. In Excel 2007, menus and toolbars are replaced by the Ribbon. It is possible to programmatically control the Ribbon, but you can’t do it from VBA. Instead, you must create a full managed code add-in using a language such as C# or Visual Basic .NET. For backwards compatibility, any menus and toolbars created in a VBA macro are put on the Add-Ins tab of the Ribbon. You probably don’t want to create new macros that put controls onto the Add-Ins tab.
The new Ribbon allows for more user interface options than menus and toolbars-a developer can add many different types of controls to a Ribbon tab. But it is also more complex, which is why you can’t customize the Ribbon from VBA. Specifically, when you create a Ribbon control, you must create custom events for the control. In VBA, you can create a procedure for any of a pre-defined list of events, but you can’t define a new event.