1. VBA Tips - Worksheet Events

Nick's picture

Using VBA, there is the functionality for Excel to track most of what a user is doing to a workbook.

Armed with this information, a system designer can make things happen that help make the user's life easier.

Today, we're going to program a simple example of tracking selection change events. i.e. when you select something different from what is currently selected, an event is triggered.

Step1: Open a blank workbook, and press ALT + F11 - this brings up the VBA editor.

Worksheet Events 

Now, double click on "Sheet1".. in blue in the picture.

- You are now in the code module of "Sheet1"

Step2: From the middle dropdown that says "General", select "Worksheet"

- VBA editor presents you with a subroutine that is designed to pick up the event that the selection has been changed.

Step3: In the empty line after "Private Sub", type the following: MsgBox "Hello" Press ALT + F11 again to return to sheet1, and press the DOWN ARROW to change the selection.

- You will now see the message box confirming that you have successfully trapped this event.

Here's an example sheet to play with

This functionality is used to great effect in my game of Noughts and Crosses, and in this Smart Table Sorting


Training Video - Worksheet Events

WorksheetEvents.xls29.5 KB