In my last article, I have introduced you with Excel events and their types. In this article, I am going to narrate you how to enter Event Handler VBA code in the relevant code module.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide and Learn Excel VBA Events Completely with 5 Tutorials.
Table of Contents
Relevant Code Module for every Event
Every event-handler procedure, I mean VBA code, must be placed in the relevant code module. If you are working with workbook related events, then the VBA code that you write for your event will be placed in the “ThisWorkbook” code module.
The code, you write for worksheet related events, will be stored in the relevant worksheet code module. For example, say you want to show some message when “Sheet1” is activated.
Excel has an event for this: “Activate” event. So, your VBA code, written for this event, will be stored in the “Sheet1(Sheet1)” code module. Look at the following code and image to see how the code is placed in the “Sheet1(Sheet1)” code module.
Activate Event Handler is created in its relevant code module.
Private Sub Worksheet_Activate()
MsgBox "Sheet1 is activated now!"
End Sub
Open the working file after downloading it. Travel from one worksheet to another one. When you will come back to “Sheet1” from another sheets, you will find the following image:
Activate Event Example.
Event-handler procedures have predefined names
Every event-handler(the procedure or VBA code that controls the event) has a predefined name.
For example, the figure below shows the code module for the “Sheet1” worksheet. I got this code module just double clicking on the “Sheet1(Sheet1)” in the Project window. Then I selected Worksheet from the left drop-down list, and finally chose and clicked on the “Activate” event.
I got a Subprocedure with two statements: first statement is just name of the Subprocedure, and second statement is “End Sub” statement like the following figure. There is no other code inside these two statements.The Subprocedure name is predefined by Excel. I wrote my event-handler VBA code between these two lines.
Event has predefined Subprocedure name.
Event-handlers with arguments
Event-handler procedures may also contain arguments. In my previous example, I have shown how to display a message when a specific worksheet is activated. In this example, I’ll explain how to work with an event that will display a message when you activate any of the worksheets.
I shall use workbook’s “SheetActivate” event. To use this event, just go to VB Editor, activate “ThisWorkbook” object in the Project window. The code window for “ThisWorkbook” opens; select “Workbook” from the left drop-down list and finally choose “SheetActivate” event from the right drop-down list. See the image below:
SheetActivate Event’s starting statement and end statement is created.
The VB Editor creates the following procedure:
Private Sub Workbook_SheetActivate(ByVal Sh As Object) End Sub
This procedure has one argument (Sh). Sh represents the activated sheet; it means that if you activate “Sheet2”, the value of Sh will be Sheet2; if you activate Sheet5, the value of Sh will be Sheet5 and so on. “Sh” is declared as an Object.
The following code makes use of information passed through the argument “Sh”. The code find out the name of the worksheet by accessing the argument’s(Sh object’s) “Name” property.
Private Sub Workbook_SheetActivate(ByVal Sh As Object) MsgBox Sh.Name & " was activated." End Sub
Open the working file after downloading it. Travel from one worksheet to another one. When you will travel from one worksheet to another, every time it will display the name of the activated worksheet like the following image:
I have activated “Sheet2” and this message displays.
Event-handler procedure with Boolean argument
Some event-handler procedures has a Boolean argument named Cancel. For example, you may plan that a pop-up message will display some information when you print something related to your workbook. There is an event called “BeforePrint” to do this. The declaration statement for “BeforePrint” event is
Private Sub Workbook_BeforePrint(Cancel As Boolean)
The default value of Cancel is FALSE. If you can make the value of Cancel to “TRUE” then printing will be cancelled. I have done in the following way:
Private Sub Workbook_BeforePrint(Cancel As Boolean) Msg = "Have you checked your printer connection?" Ans = MsgBox(Msg, vbYesNo, "About to print...") If Ans = vbNo Then Cancel = True End Sub
Open the working file after downloading it. Travel from one worksheet to another one. When you will travel from one worksheet to another, every time it will display the name of the activated worksheet like the following image:
The “Workbook_BeforePrint” procedure executes just before the workbook prints. When you will try to print something, this event-handler procedure will display a message to let you know whether you have checked the printer connection. If you press Yes (Cancel is TRUE), printing will start; if you press No (Cancel is FALSE), nothing will be printed. Download the file below, try to print the content of the worksheet and you will find a message box like the following image:
BeforePrint event is executed just before printing something.
I am explaining another important job, you can do with “BeforePrint” workbook event. Say you want to print the content of Excel cell “A1” in the page headers or footers. The following example shows a code that make possible to print the content of cell A1 in the page header.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterHeader = Worksheets(1).Range("A1")
End Sub
So, this was all from me on this topic. Let me know in case you face any issues while using this article.
Happy Excelling ☕
Download Working File
Download the working file from the link below:
Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!
