VBA Code Modules & Event Procedures for Sheet & ThisWorkbook
11

VBA Code Modules & How to Run Macros Based on User Events

Bottom line: In this post we compare the different places to store macros, functions, & VBA code in Excel.  We specifically look at the Code Modules, Sheet Module, and ThisWorkbook Module to learn the differences between how each works.  We also learn how to run macros based on events or actions by the user.

Skill level: Intermediate

5 Modules to Store VBA Macro Code in a Project Workbook2

Video: VBA Code Modules Explained

 

Watch video on Youtube & give it a thumbs up. 🙂

Checkout my FREE webinar on “The 7 Steps to Getting Started with Macros & VBA”.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to register for the webinar, it’s FREE!

Download the File

Download the Excel file to follow along.

VBA Code Modules.xlsm (25.3 KB)

5 Places to Store VBA Code in a Workbook

There are actually 5 different modules where we can store VBA code in a workbook.  We can see each of these in the Project Explorer window (Ctrl+R) in the VB Editor.  Here is a quick overview of each object type.

  1. Code Modules – The code modules are the most common place we store macros.  The modules are located in the Modules folder within the workbook.
  2. Sheet Modules – Each sheet in the workbook has a sheet object in the Microsoft Excel Objects folder.  Double-clicking the sheet object opens its code module where we can add event procedures (macros).  These macros run when the user takes a specific action in the sheet.
  3. ThisWorkbook Module – Each workbook contains one ThisWorkbook object at the bottom of the Microsoft Excel Objects folder.  We can event based macros that run when the user takes actions in/on the workbook.
  4. Userforms – Userforms are interactive forms or windows where we can add controls like drop-down menus, list boxes, check boxes, buttons, etc.  Each userform is stored in the Forms folder and has a code module where we can put macros that will run when the form is open, and/or the user interacts with the controls on the form.
  5. Class Modules – Classes are stored in the Class Modules folder and allow us to write macros to create objects, properties, and methods.  Classes can be used when we want to create custom objects or collections that don’t exist in the Object Library.

When we double-click or right-click> View Code (keyboard shortcut: F7) on any of these objects in the Project Explorer Window, the code window opens on the right side of the VB Editor.  The code window looks the same for each of the objects.  It is just a big blank canvas where we can type code.

Double Click a Code Module to View Code Window in VBA Editor

For this post we are going to compare the first 3 modules listed above.

Code vs Sheet vs ThisWorkbook Module

These are probably the 3 most common places we can store macros (sub procedures) and functions (UDFs) in our VBA projects.

Each of these modules allow us to store macros that we can run by pressing a button or from the Macro window.  However, the Sheet and ThisWorkbook objects allow us to store event procedures (macros) that will run when a user takes an action in the workbook.

So let’s take a look at each object in more detail.

Code Modules

Code Modules allow us to store regular macros (sub procedures) and functions (User Defined Functions, UDFs).  This is the typical place we start writing and storing our macros.  The macro recorder also creates code in a code module.

Once we have a macro in a code module, we can run it from the Macro window (Developer tab or View Tab > Macros button).  We can also run the macro by assigning it to a button or shape.  When the user clicks the button, the macro will run.  Checkout my article and video on how to create a Personal Macro Workbook and add buttons to the ribbon for more details.

Overview of Code Module for VBA Macros in VB Editor1

We can add multiple code modules to the Modules folder, and this helps keep our code organized within a project (workbook).

So, the code module allows us to store basic macros that will be run by the user pressing a button.  But what if we want our macros to automatically run when the user opens a workbook, changes worksheets, or selects a specific cell?

Sheet Modules & Event Procedure Macros

Each sheet in the workbook has a sheet object in the VB Editor.  These are listed in the Microsoft Excel Objects folder for each open workbook in the Project Window.

The sheet object displays the sheet’s code name and tab name in parentheses.  The tab name is the name that is displayed in the workbook that the user can change.

Double-clicking a sheet object will open it’s code module window.

Overview of Sheet Module for VBA Macros in VB Editor

We can add regular macros in the sheet object.

We can also add Event Procedures that will run when a user takes an action on the worksheet.  Here’s how to add an event procedure.

  1. Select Worksheet from the Objects drop-down menu.  The Selection Change event will automatically be added to the code window.  This is the default event, but we do NOT have to use this event.  The SelectionChange event runs every time the user selects a cell in the sheet.
  2. Click the Procedure drop-down to see a list of the other event procedures.
  3. Select one of the events from the list and it’s procedure (macro) code will be added the sheet’s code module.  We can now add code inside the procedure that will run when the user takes an action.

Add a VBA Event Procedure to the Sheet Code Module in the VB Editor

In the video I use a code example that selects the entire row and column of the cell that is selected in the worksheet.  This selects/highlights the entire row and column for the user.  Checkout the video for more details on how it works.

VBA Macro to Select Entire Row and Column On Cell or Range Selection - SelectionChange Event

The sheet modules can contain multiple event procedures.  It can also contain regular procedures (macros) and functions.

There is a list of all the worksheet events and their help articles on the MSDN Worksheet Object Help Page.

ThisWorkbook Module & Event Procedure Macros

Each workbook contains one object called ThisWorkbook.  The ThisWorkbook object is stored at the bottom of the Microsoft Excel Objects folder.

The main purpose of the ThisWorkbook module is to run event procedures (macros) when the user takes an action in the workbook.  This could be when the user opens the workbook, before they close the workbook, when they select a worksheet, range, or even update a pivot table.

Overview of ThisWorkbook Module for VBA Macros in VB Editor1

The ThisWorbook module is very similar to the sheet modules.  The main difference is that the event procedures in the ThisWorkbook module can run when actions are taken throughout the entire workbook.  The sheet module’s events only run when actions are taken on the specific sheet that the code is in.

The process to add event procedures to the ThisWorkbook module is the same as the sheet module.

  1. Select Workbook from the Object drop-down.
  2. The Workbook_Open Event is automatically added to the module.  This macro will run when the workbook is opened and macros are enabled.  You can delete this event’s code if you do not want to use it.
  3. Select another event from the Procedure’s drop-down.
  4. The code for that procedure will be added to the module.  Add code to the procedure that will run when the user takes that action in the workbook.

The Worksheet events are also made available in the ThisWorkbook list of events.  This means we can run worksheet events on any worksheet, or specific worksheets, instead of copy/pasting code between sheet modules.

There is a list of all the workbook events and their help articles on the MSDN Workbook Object Help Page.

What’s the Best Place to Store My Code?

Now that you know how the different modules work, you might be wondering where the best place to store all your code is.  And the answer is, it’s a matter a personal preference…

Put All the Code in Sheet Modules

Technically, you can store all your macros and functions in a Sheet module or ThisWorkbook module.  Some developers like this approach because it keeps everything tied to a specific sheet or set of sheets.  The sheets can then be moved or copied to different workbooks, and the code will travel with it.  It’s important to note that UDFs must be stored in a code module.

Organize Code in Code Modules

Other developers like to have all macros organized in the code modules.  If they need to use sheet or workbook events, they add those events to the appropriate module and then call macros in the code modules from those events.  The advantage here is that it is easy to see all your code in one place.  You can add comments to macros that are called by event procedures so you know how the macros are run.

Personally, I prefer storing my code in code modules for this reason.  When I open a workbook in the VB Editor it’s easy to see that the macro contains code, without having to double-click each sheet object to see if code exists in the module.  This can be time consuming if the workbook has a lot of sheets, and can make debugging a project more difficult.

Plan Your Legacy Wisely!

There are pros and cons to each method, so my suggestion is to pick the one that works for you.  If you are going to be passing on the workbook or project to someone else to maintain in the future, then try to make it easy for them to find your code.  Planning your legacy ahead of time will mean less work for you in the future…

Please leave a comment below with any questions.  Thank you! 🙂

Free Webinar on Macros & VBA

My free webinar on “The 7 Steps to Getting Started with Macros & VBA” is going on now, and it’s FREE!

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to register for the FREE webinar!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 11 comments
K.Theru Sakthe Kumar - September 1, 2017

Hai.

I would like to write a macro to copy the value from the last row of all the sheets in a workbook into a single sheet in the same workbook.

Could you help me?

WBR,
KTSK

Reply
Monday Imarhiagbe - August 17, 2017

Thanks for your article, I am looking for how to create 2 vba procedure in “Thisworkbook” the two procedure are:

1. A procedure to open an excel file on your choice worksheet

2. The second procedure is to make an excel file expired after certain period of time and automatically delete the file.

please is there a way to do this, kindly help me out.

Reply
rolo - June 17, 2017

Great examples Jon!

Can you post some examples on how to run events from Addin?

For example: sheet change event triggered from an addin

Thanks!

Reply
Diane - February 21, 2017

Hi

I just watched the 3 videos on Pivot tables and Dashboards. The videos were very well done and easy to understand. I can’t wait to start creating!

I also want to learn how to add a “refresh” button right in my workbook that pulls in new data as I want to update. I have used this at a past employer, but I do not know how to do it. Is there a tutorial for this type of function?

Thanks, I look forward to future learning!

Diane

Reply
Sean - January 27, 2017

Thank you for the videos they were very well done and informative, the best I have seen.

I am trying to determine if I am able to use VLOOKUP to weight the 1 or more possibilities in the multiple dropdown list in the cell. I have several cells that have multiple option in the cell.

I have attached a copy of the code I used and modified.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
‘do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 12 _
Or Target.Column = 13 _
Or Target.Column = 16 _
Or Target.Column = 18 _
Or Target.Column = 19 _
Or Target.Column = 21 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘ do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

Any advise would be helpful,

Reply
    Jon Acampora - February 4, 2017

    Hi Sean,
    You can use the Vlookup function in VBA. Here is the code.

    WorksheetFunction.Vlookup(arg1,arg2,arg3,[arg4])

    The arguments will be the same as those in Excel. You can specify a range in a worksheet for the Table_Array (lookup range). So you might want to add a lookup table to a sheet in your workbook and use that code in VBA to weight the values.

    You can also store the values in an array in VBA. There are a lot of options for this, but the Vlookup one should be pretty easy to implement. I hope that helps get you started.

    Reply
Peter Buyze - January 21, 2017

Jon, I took the liberty once again to post about this article on G+. My post is here https://plus.google.com/+PeterBuyze/posts/Rmid3Wa1FTt

Reply
David - January 20, 2017

Enjoyed the lesson. Very clear.

Thanks

David

Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x