• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Workbook & Worksheet Events in Excel VBA

You are here: Home / Excel VBA / Workbook & Worksheet Events in Excel VBA
Workbook & Worksheet Events in Excel VBA
November 19, 2014 by Philip Treacy

What Are Events?

An Event occurs when certain actions are performed by the user or VBA. For example when you open a workbook the Workbook_Open event is triggered. When you (or your VBA) saves a workbook, the Workbook_BeforeSave event is triggered.

These events are built into Excel VBA and are executed automatically by Excel. You can write your own code which is also executed when these events occur. This code is known as an Event Handler.

In this article I’m going to look at events for worksheets and workbooks, but there are also events specific to the Excel application itself, user forms, and charts.

What Events Are Available?

For each object there are specific events available. An easy way to check is to let the VBA editor show you.

Worksheet Events

Right click on a sheet tab and choose β€˜View Code’

View code from sheet tab

This will open the VBA editor with your sheet module already selected. Below you can see that I have the Sheet1 module selected.

Sheet module

In the code window on the right hand side of the editor window, click on General and from the drop down click on Worksheet.

Select worksheet

You’ll notice that the drop down on the right side has now changed, and is showing the SelectionChange event. The editor has also entered the Worksheet_SelectionChange event declaration for you.

Default worksheet event

If you click on the right hand drop down, you will see a list of all the events for the worksheet. Clicking on any of these will enter the event code declaration for you into the code window.

Worksheet event list


Workbook Events

For workbook events, double click on ThisWorkbook on the left hand side (in the Project Explorer)

ThisWorkbook

and on the right hand side (the code window) select Workbook from the left hand drop down.

Select workbook

You should now have the declaration for the Workbook_Open event in your code window.

Default workbook event

As with the worksheet, clicking the right hand drop down gives you a list of events for the workbook and clicking on these will enter the code declaration for that event.

Workbook event list

Location of Event Code

Before we go any further let’s talk about where the event code must be stored. With β€˜normal’ VBA code you’d create a new module and enter the code in there. But with events, VBA expects the code to be in specific places. If you put it in a β€˜normal’ module, it won’t work.

If you want to write an event handler for a worksheet the code must be in the sheet module for the relevant sheet. Each sheet has its own module. Double click the sheet name in the Project Explorer in the VBA Editor to access the sheet module.

Likewise, workbook level code must be in the ThisWorkbook module.

Writing VBA for Worksheet Events

You can write whatever code you like and put it into the event procedure declaration. When an event occurs it is said to be raised and when this happens, Excel runs whatever code it finds associated with that event.

So let’s write some code to execute when the Worksheet_SelectionChange event is raised. We’re going to change the color of the currently selected cell (the ActiveCell) to green


Cells.Interior.Color = xlColorIndexNone

ActiveCell.Interior.Color = RGB(117, 173, 33)

OK so I can now make a cell a nice, bright green, but that’s not a huge lot of use I suppose. Let’s do something more useful, like indicate the currently selected cell by putting a border around the current row and the current column


Cells.Borders.LineStyle = xlLineStyleNone

ActiveCell.EntireColumn.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

ActiveCell.EntireRow.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

You could combine both pieces of code to change the color of the ActiveCell, and put a border around the current row and column.

Highlight row and column

Parameters

You may have noticed that the declaration for the Worksheet_SelectionChange event specifies a parameter, Target, that is passed into the Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

This parameter is a range and you can use it in your code to test things like how many cells are selected, or which column is selected, or some value or property of the selected range. I’m going to test how many cells are selected and if there are more than 1, I’m going to exit the sub. To do this I just add one line of code


If Target.Cells.Count > 1 Then Exit Sub

So my event code now looks like this


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Cells.Borders.LineStyle = xlLineStyleNone

ActiveCell.EntireColumn.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

ActiveCell.EntireRow.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

End Sub

If you use your mouse to select more than one cell, nothing happens because I am checking the Target parameter and can tell that there is more than 1 cell selected.

Other events pass in different parameters, but all are there for you to test/use as you see fit.

Useful Worksheet Events

Below are some useful worksheet events, but you can read more about the available events at Microsoft

  • Worksheet_Activate
  • Worksheet_BeforeDelete
  • Worksheet_Change
  • Worksheet_Deactivate

Writing VBA for Workbook Events

Let’s look at a couple of examples of workbook events. The first is Workbook_Open which is raised when, you guessed it, the workbook is opened. We could write something like this.


Private Sub Workbook_Open()

MsgBox "Welcome back, I missed you!"

End Sub

You can of course put whatever code you like in there and it will run when the workbook is opened.

But, hang on you say, what if macros are disabled?

Well yes, then the macro won’t run, but that is a topic for another day. Let’s for now assume that you have things set up so that you either are opening workbooks from Trusted Locations and the macros will run, or you have your users educated to know to enable macros on your workbook(s).

Next we want to do something when a user tries to add a sheet. Let’s say we want to ask them what they want to call the sheet. We can use a simple InputBox to ask them for the new sheet name, and rename the sheet for them.


Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim NewSheetName As String

NewSheetName = InputBox("What do you want to call this sheet?", "Please enter the new sheet name")

If NewSheetName <> "" Then Sh.Name = NewSheetName

End Sub

Note that I use Sh.Name = NewSheetname to change the name of the new sheet. Sh is the parameter passed into the Workbook_NewSheet event sub and is actually the new sheet object.

I use an IF statement to test if the user has entered anything. If they hit the ESC key or click Cancel, then a zero length string is returned, and this can’t be the sheet’s new name. So this


If NewSheetName <> ""

checks if a zero length string (nothing) has been entered. If it has, then I don’t try to rename the sheet.

Don’t forget that code for your workbook events must go into the ThisWorkbook module.

Useful Workbook Events

Some useful workbook events are listed below, and further info on workbook events can be found at Microsoft's MSDN site

  • Workbook_BeforeSave
  • Workbook_BeforePrint
  • Workbook_AfterSave
  • Workbook_SheetActivate
  • Workbook_SheetCalculate
  • Workbook_NewSheet
  • Workbook_SheetChange
  • Workbook_BeforeClose
  • Workbook_Open

Further Reading

This link is for an article about 2010 Events, but is generally applicable to 2007/2010 and 2013.

Running VBA for Excel 2010 Events

Sample Code

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

All the code I've written is available in an xlsm, an xls (Excel 97-2003) or in text format.

Please download and have a play around with your own events. Let me know if you have any questions or get stuck, and I'd love to hear your comments below.


Workbook & Worksheet Events in Excel VBA

More Excel VBA Posts

automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA

Excel Progress Bar for VBA

Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category: Excel VBATag: Excel VBA
Previous Post:My 2014 Microsoft MVP Summit Experience
Next Post:Excel Advent CalendarExcel Advent Calendar

Reader Interactions

Comments

  1. Matthew

    April 22, 2020 at 5:17 pm

    Dear Philip,
    I really like this! Thank you for sharing the tip.
    One question, when this is activated, the F4 repeat last action shortcut no longer seems to work. I use this to format cell by cell (Fill Color light green) to show that I have performed my check of each cell with another source.
    Is there any way to reinstate the F4 key?
    Kind regards
    Matthew

    Reply
    • Philip Treacy

      April 23, 2020 at 2:50 pm

      Thanks Matthew,

      With the code in Worksheet_SelectionChange making changes to the sheet, F4 is affected. But if you remove this code in Worksheet_SelectionChange F4 should be fine. I’m not sure what even code you are running though. happy to look at it if you open a topic on the forum and attach the workbook.

      Regards

      Phil

      Reply
  2. Michel Delruelle

    April 15, 2020 at 3:26 pm

    Dear Philip,
    Very interesting and clear information, thank you very much for that. Is it possible create a workbook in vba included event code? I didn’t find anything for that,
    Kind regrads,(

    Reply
    • Philip Treacy

      April 16, 2020 at 2:36 pm

      Thanks Michel,

      Do you mean create a workbook from inside an event? Yes, just use the normal Workbooks.Add. If you are after something more than that please start a topic on our forum and ask your qs there.

      Thanks

      Phil

      Reply
  3. John Hogerheide

    October 3, 2019 at 4:27 am

    I am creating dynamic TExt and listboxes, I am trying to figure out how to capture events from those boxes. Do you have any suggestions on a book or online class that is specific to advanced event handling. I am very comfortable using excel events on worksheets, Userform etc…its the dynamic boxes where is need assistance….any suggestions???? Ideas??

    Reply
    • Catalin Bombea

      October 4, 2019 at 5:51 am

      Hi John,
      You have a good resource here.

      Reply
  4. Chris

    September 22, 2018 at 12:39 am

    I cannot get ANY of these events to work at all. No matter what I do.
    When I change, save, close, open, WHATEVER – Excel acts like I have absolutely no VBA code whatsoever under these events. YES, macros are enabled, YES, Events are enabled, I’m at a total loss. NO CLUE as to what’s going on.

    Reply
    • Catalin Bombea

      September 22, 2018 at 1:34 pm

      There is one more thing you need to check: are your codes into ThisWorkbook module? They will not work from any other place, if you inserted a new module and pasted the code in there, it will not work.

      Reply
  5. Saba

    October 18, 2016 at 9:11 pm

    I have a macro in excel file saved in startup folder,can I make the macro to run again when the system wakes up after x hrs of hibernation.?

    Reply
    • Philip Treacy

      October 20, 2016 at 9:44 am

      Hi Saba,

      The files in your Excel Startup will only be read when Excel starts. When the PC comes out of hibernation, that doesn’t happen so the macro won’t execute.

      You could try using a scheduler program to trigger on resume and run Excel, and hence the macro that way.

      Phil

      Reply
  6. John Campbell

    November 25, 2015 at 1:29 am

    WOW!! – I can’t quite believe I’ve stumbled upon this site / article. For some years I’ve thought of myself as the “office Excel guru”… After reading this article and finding additional information from your site, I actually feel like an Excel virgin!!

    Simply, love it!! – The information is very well written, informative & clear. I’ve found myself inspired to spending (way too much work) time, experimenting, tweaking the code and observing the outputs.

    Reply
    • Philip Treacy

      November 25, 2015 at 10:17 am

      Hi John,

      Thank you for your kind words. Great to hear you are finding the site useful, there’s plenty of helpful information on here πŸ™‚

      Regards

      Phil

      Reply
  7. Rene Guyton

    November 23, 2014 at 2:51 am

    I had a lot of fun playing with these examples. I got a bit goofy with the naming a NewSheet one and added a bit where it would test for a specific word and pop up a MsgBox saying ‘Can’t name it that!! Try Again.’ and going back to the InputBox.

    I have been having a hard time getting back into coding and even though my example above isn’t for a professional assignment, I was impressed with how the writing of the post encouraged me to go right in and try the code, play around with it and reach out to figure other things out. Thank you! πŸ™‚

    Reply
    • Philip Treacy

      November 23, 2014 at 8:42 pm

      Hi Rene,

      Thanks for letting me know how you have played around with this code – it is humbling to hear that something I wrote can encourage you to do that πŸ™‚

      I’m really glad that you have found it fun too – I think that is very important. So even if you are being a bit goofy, its still good practice.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x