Userform Events
What are Events?
Anything you do in to trigger an excel file is an event (an action). Example: If you want a greeting message ‘Good Day’ whenever an excel file is opened. Or if you want to capture the date and time automatically whenever a specific range of cells are altered etc.
Few examples of excel event are as follows:
- Opening a workbook
- Going to a specific worksheet
- Editing a worksheet
- Entering data
- Saving/closing file
We can use these events by adding VBA codes (event handlers) to make excel perform in a certain way.
Userform Events
Userform events trigger wherever you initialize or display the form, drag and drop the controls and perform some actions. Some of the most used workbook actions that trigger the Event-
Activate: When you activate or display the userform.
AddControl: When any userform’s control is added at the run time
BeforeDragOver: When the mouse pointer is hovering over the form and the drag and drop property is in development.
BeforeDragOrPaste: When you click the left button of the mouse, and the data is either dropped or pasted.
Click: When the left button of the mouse is clicked while the mouse pointer is over the userform.
DblClick: When the left button of the mouse is double clicked while the mouse pointer is over the userform.
Deactivate: When the userform loses focus because a subform is displayed.
Initialize: When you load the userform into memory (or you have run the userform’s code).
KeyDown: When a key is pressed while exhibiting the userform.
KeyPress: When you press an Ansi key while exhibiting the userform.
KeyUp: When you release a key while exhibiting the userform
Layout: When the size of the userform or any control is altered at run-time.
MoveDown: When the mouse button is pressed while the mouse pointer is over the userform.
MouseMove: When the mouse pointer is moved from one place to another over the userform.
QueryClose: Before you close the userform or unload it from the memory.
RemoveControl: When you remove any control from the userform at run time.
Resize: When you resize the userform.
Scroll: When you scroll the userform.
Terminate: When userform is removed from memory to clean up the memory space.
Zoom: When you zoom the userform.
Example 1
‘UserForm Deactivate event Sub UserForm_Deactivate() UserForm.Caption = "Deactivating the UserForm!" End Sub
Example 2
‘UserForm click event Sub UserForm_Click() Load UserForm UserForm.StartUpPosition = 2 UserForm.Show End Sub
Example 3
‘UserForm click event Private Sub UserForm_Resize() UserForm1.Caption = "Click me to resize" End Sub
Workbook Events
Events for workbooks need to be stored in the ‘ThisWorkbook’ module whereas events for a worksheet need to be stored in the code module for that specific sheet only.
To look at Workbook events –
• Open Visual Basic editor
• Go to ‘ThisWorkbook’ module
• From the left drop-down, select ‘Workbook’
• Now you will notice that on the right drop-down, ‘Open’ is present and in the program editor a code shell has been automatically appeared (starts with Private Sub and ends with End Sub)
• We can add anything we want excel to perform whenever the excel file is opened
• In order to look at other events available, go to the right-drop down and click on the ‘Combo Box’ to see more options
Some of the most used workbook actions that trigger the
Event-
• Open - The workbook is opened.
• Activate - The workbook is activated.
• BeforeClose - The workbook is about to be closed.
• BeforeSave - The workbook is about to be saved.
• NewSheet – Whenever a new sheet is created in the workbook.
• SheetActivate – Whenever any sheet in the workbook is activated.
• SheetChange - Any worksheet in the workbook is edited/changed by the user
• WindowActivate - Any window of the workbook is activated.
Workbook Open Event Examples
This event is triggered when the workbook is opened, and this executes the Workbook_Open procedure. As stated earlier, it can be used to achieve a lot of things like –
• A Greeting Message
• Activating a specific sheet or a specific cell in a sheet
• Saving date and time or name of the user who opened the workbook
But for Workbook_Open event to fire, VBA macros should be enabled – so if the default setting is that the macros are disabled, whenever you open the file, the event will not execute on its own (till the time the macros are not enabled).
The following example gives a greeting message whenever someone opens the file with today’s date –
Private Sub UserForm_Resize() UserForm1.Caption = "Click me to resize" End Sub
Open Event Examples
This event is triggered when the workbook is opened, and this executes the Workbook_Open procedure. As stated earlier, it can be used to achieve a lot of things like –
• A Greeting Message
• Activating a specific sheet or a specific cell in a sheet
• Saving date and time or name of the user who opened the workbook
But for Workbook_Open event to fire, VBA macros should be enabled – so if the default setting is that the macros are disabled, whenever you open the file, the event will not execute on its own (till the time the macros are not enabled).
The following example gives a greeting message whenever someone opens the file with today’s date –
Example 1
Private Sub Workbook_Open()
Msg = “Hello!! ” & Now()
MsgBox Msg, vbInformation
End Sub
Below is an example of saving a person’s name when he opens the file – as soon as the person opens an excel file which has the below macro, VBA will open another file ‘Module 10 - VBA.xlsx’ which will store the name of the person who opened the file along with the time. This type of code is used a lot to keep an audit trail of who accesses the file -
Example 2
Private Sub Workbook_Open()
Dim xOpened As String
Dim xtime As String
xOpened = Environ("Username")
xtime = Format(Now, "dd/mm/yyyy hh:mm")
Workbooks.Open ("C:\PATH TO SECOND FILE\Module 10 - VBA.xlsx")
Workbooks("Module 10 - VBA.xlsx").Activate
i = Workbooks("Module 10 - VBA.xlsx").Sheets("Audit").Range("A65536").End(xlUp).Row
Workbooks("Module 10 - VBA.xlsx").Sheets("Audit").Cells(i + 1, 1).Value = xOpened
Workbooks("Module 10 - VBA.xlsx").Sheets("Audit").Cells(i + 1, 2).Value = xtime
Workbooks("Module 10 - VBA.xlsx").Save
Workbooks("Module 10 - VBA.xlsx").Close
End Sub
You can also use conditional constraints or loops –
Example 3
Sub Workbook_Open()
If Weekday(Date, vbSunday) = 2 Then
Msg = "Hello!!" & Now
MsgBox Msg, vbInformation
End If
End Sub
We can also select a specific cell when the excel file is opened –
Example 4
Private Sub Workbook_Open()
Worksheets("Sheet1").Range("D4").Select
End Sub
Newsheet Event
The following procedure executes whenever a new sheet is added to the workbook. The sheet is passed to the procedure as an argument/parameter. Because a new sheet can be either a workshe or a chart sheet, this procedure determines the sheet type. If it’s a worksheet, it inserts a date and time stamp in cell A1.
Example 1
Private Sub Workbook_NewSheet(ByVal Sh As Object)
If TypeName(Sh) = “Worksheet” Then _
Range(“A1”) = “Sheet added “ & Now()
End Sub
BeforeSave Event
This event triggers just before the workbook is saved – so as soon as you press Cntrl+S or click on Save button, the event will trigger.
Below is an example of a file which is being saved for the first time because that will throw the prompt of ‘Save As’ (if a file is in read-only mode, then also the below event will trigger) –
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "Save the file with xlsm i.e. macro-enabled format for enabling macros."
End If
End Sub
If the Save As box appears, i.e. the file is being saved for the first time (or in read-only mode).
WorkSheet Events
These events are for individual worksheets and are hence stored in the code module of the worksheet for which we plan to use them –
• Activate - The worksheet is activated.
• BeforeDoubleClick - The worksheet is double-clicked.
• BeforeRightClick - The worksheet is right-clicked.
• Calculate - The worksheet is calculated (or recalculated).
• Change - Cells on the worksheet are changed by the user.
• PivotTableUpdate A PivotTable on the worksheet has been updated.
• SelectionChange The selection on the worksheet is changed.
Worksheet_Change Event
It will trigger whenever a specified range or cell in a worksheet is changed by the user (not by a formula) – the code below will add the current date and time to column A of the row where change has taken place. The range we are interested in is from column B to column G – so if a user makes any changes to say cell B5, current date and time will be added to A5 – any changes to cell G10 and date and time will be added to A10 (and so on..)
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:G")) Is Nothing Then
Cells(Target.Row, 1) = Now
End If
End Sub
Target argument/parameter has been defined as a Range object and refers to the cells which were changed – so if the user is making any changes to cell B5 – target variable will hold the value B5 – using intersect function, VBA then checks if there is an intersect i.e. common cell between target and range we have specified – and using ‘Not’ and ‘Nothing’ we are just saying that if there is an intersect, we want to add the date and current time to the row in which change took place.
Worksheet_Activate Event
This event executes whenever we go back to a specific sheet
(which has the VBA code in its module) – the below example just throws a
message box cautioning the user against deleting any formulas while he is on
this sheet –
Private Sub Worksheet_Activate()
MsgBox "Do not delete the formula", vbOKOnly
End Sub