' Indicate the Application-level event occurred. Private Sub m_app_SheetActivate(ByVal Sh As Object) ' (3) ' Indicate the Workbook-level event occurred. ' Initialize the object variable if it was not already. Private Sub Workbook_SheetActivate(ByVal Sh As Object) Visual Basic adds the object variable to the Code window’s object and event lists, which you can then use to add event procedures.įor example, the following code from the Workbook class creates an object variable m_app for the Application object, initializes that object in the Workbook_Activate event, then uses an Application-level event: Dim WithEvents m_app As Application ' (1) So, if the SheetActivate event is handled at all three levels, the Worksheet-level event procedure runs first, then the Workbook-level event procedure, and finally the Application-level procedure.Īre built in to the Code window for Workbook and Worksheet objects ( Figure 4-20), but how do you use events for other objects, like Application? To do so:ĭeclare a variable for the object using the WithEvents keyword. In this case, the Application-level event is the most general event handler since it receives SheetActivate events from all sheets in all open workbooks the Workbook-level event is next, receiving SheetActivate events only from sheets in the open workbook and the Worksheet- or Chart-level events are the most specific, receiving the Activate event only from that specific Worksheet or Chart object.Įvents occur at the most specific level first then move up to more general levels. For example, the SheetActivate event occurs for the Application, Workbook, Worksheet, and Chart objects (for Worksheet and Chart, it’s simply called the Activate event).

You’ll notice that some of the same events occur for a number of objects.

Visual basic for excel on change event