Using Excel Events
Excel events cover a wide range of activities at the Application, Workbook, Worksheet, Form and other object levels. A good starting point to read about events is in the following link:
Besides events, two special modules can be created in the General Module area that will execute at workbook startup and workbook shut down. The link to that discussion is Here
The most common usage of events is probably in the UserForm area. Events triggered by the User Form area include:
- Activate
- Initialize
- QueryClose
The user can also be alerted to any cells changes in a worksheet using the Worksheet_Change event. The examples below will show a few event processing subroutines.
Program Code
' *************************************************************
' *************************************************************
' These Are User Form Event Processors
' The Code is located in the form code area
' *************************************************************
' *************************************************************
Option Explicit
Option Base 1
' *************************************************************
' This event will execute when a form is started
' but will not if the form is made visible from a hidden status
' *************************************************************
Private Sub UserForm_Initialize()
MsgBox ("User Form Initialized")
End Sub
' *************************************************************
' This event will execute at form startup or when the form
' is made visible from a hidden status
' *************************************************************
Private Sub UserForm_Activate()
MsgBox ("Activated")
End Sub
' *************************************************************
' This event will execute when the user "X"s out of the form
' *************************************************************
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
MsgBox ("Query Close")
End Sub
' *************************************************************
' *************************************************************
' These Are Workbook Event Processors
' The Code is located in the ThisWorkbook area
' *************************************************************
' *************************************************************
Option Explicit
Public WithEvents App As Application
' *************************************************************
' Activated when a new blank workbook is created
' *************************************************************
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox ("New Workbook")
End Sub
' *************************************************************
' Activated when the current workbook is closed
' *************************************************************
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Workbook Closing")
End Sub
' *************************************************************
' Activated when the current workbook is opened
' *************************************************************
Private Sub Workbook_Open()
MsgBox ("Workbook Open")
Set App = Application
End Sub
' *************************************************************
' *************************************************************
' These Are Worksheet Event Processors
' The Code is located in a specific worksheet area
' *************************************************************
' *************************************************************
' *************************************************************
' Activated when switching to the worksheet (not at startup)
' *************************************************************
Private Sub Worksheet_Activate()
MsgBox ("Worksheet Activated " & ActiveSheet.Name)
End Sub
' *************************************************************
' Activated when a cell is changed on a worksheet
' Very useful!!!!
' The example below shows the cell value and address
' *************************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox ("Worksheet Change " & Target.Cells(1, 1).Value _
& " " & Target.Address)
End Sub
' *************************************************************
' Activated when switching FROM a worksheet
' *************************************************************
Private Sub Worksheet_Deactivate()
MsgBox ("Worksheet Deactivated")
End Sub
' *************************************************************
' *************************************************************
' These Are Form TextBox Event Processors
' The Code is located in the form
' Other form objects (combo boxes, list boxes) are similar
' *************************************************************
' *************************************************************
' *************************************************************
' Activated when the user enters the text box
' *************************************************************
Private Sub TextBox1_Enter()
MsgBox("Text Box Enter")
End Sub
' *************************************************************
' Activated with each character change in a text box
' *************************************************************
Private Sub TextBox1_Change()
MsgBox("Text Box Change")
End Sub
' *************************************************************
' Activated when the user leaves the text box
' *************************************************************
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox("Text Box Exit")
End Sub