Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

EVENT-HANDLING MACROS IN EXCEL VISUAL BASIC

Part one of a five-part series of blogs

Want to do something when a user opens a workbook, or stop them clicking on a particular cell? You need to learn how to attach code to Excel workbook or worksheet events!

  1. Introduction to Handling Events in Excel VBA Code (this article)
  2. Workbook Events
  3. Events for a Particular Worksheet
  4. Bypassing Macros
  5. Considerations for Other MS Office Application Events

This series of blogs is part of our Excel VBA online tutorial.  If you want to learn more, have a look at our classroom-based courses in VBA macros and/or Excel.

Posted by Andy Brown on 22 November 2011 | 2 comments

Introduction to Handling Events in Excel VBA Code

When asked for what could blow his programme off course, Harold Macmillan famously said: "Events, dear boy, events" (or did he?).  Much the same can be said of your program in Excel VBA.

Whenever you open, save or close a workbook, print something out, click on a cell, type something in or right-click or double-click, events happen - and to each and every one of these events you can react with judiciously written code.

An Example - Opening a Workbook

Suppose that you want to make sure that when someone using your workbook opens it, a macro automatically takes them to the correct worksheet.  You could do this with the following event-handling macro:

Private Sub Workbook_Open()

'go to the correct worksheet on opening

Worksheets("ImportantSheet").Select

End Sub

So: how do you write macros like this?

Creating an Event-Handling Macro

The first step is to decide whether your macro is going to handle an event to do with the entire workbook, or a single worksheet.  Here are some examples:

Scope of event Examples
Workbook Opening, saving or closing a file, printing anything
Worksheet Clicking on a cell, changing a cell's value, right-clicking on a cell

A fuller list of examples is given later in this blog.  For our example, we want to attach code to the workbook, so you need to double-click on ThisWorkbook:

ThisWorkbook selected

Double-click on the workbook in the code window (or right-click on it and choose to view its code).

 

This is almost the only time in Excel VBA that you should write code in anything other than a module.

You can now choose to attach code to the workbook itself (there's actually no other choice, so it's annoying that this isn't selected automatically for you):

Selecting Workbook object

Click on the drop arrow where it says General, and choose Workbook.

 

You can now choose the particular event you want to handle by clicking on the right-hand drop arrow:

Workbook event list

Choose from the list of possible events which can happen in a workbook's life.

 

Note that on this occasion there was actually no need to do this, as Excel guessed that it was the workbook's Open event that you wanted to intercept and "handle".

You can now type in any code that you want to run when a user opens your workbook!  When you save your file and re-open it, then all things being well your code will run.

Cancelling Events (Before and After Events)

Whenever an event begins with the word Before it means that you can either react to the event which is about  to occur and - if desired - prevent it happening.  Here's an example which stops anyone closing workbooks if they haven't filled in a version control number.

Option Explicit

'holds the current version number

Private VersionNumber As Integer

Private Sub Workbook_Open()

'read in the version number in cell A1

Worksheets(1).Select

VersionNumber = Range("A1").Value

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim NewVersionNumber As Integer

'if version number hasn't changed, can't close

Worksheets(1).Select

NewVersionNumber = Range("A1").Value

If VersionNumber = NewVersionNumber Then

'user hasn't changed version number - abort

MsgBox "Must update version number first"

Cancel = True

End If

End Sub

Here's what this macro does:

  • Whenever anyone opens the workbook, the code attached to the Open event reads the value of cell A1 on the first sheet into a private variable called VersionNumber.
  • When someone then tries to save the workbook, code attached to the BeforeSave event checks to see if the user has updated this version number, and if not displays a message and cancels the event.

In the above code, the line:

'cancel this event

Cancel = True

will cancel whichever event it was which triggered the macro (in this case, saving the workbook).

Workbook and Worksheet Events

Now that we've seen how to create events and cancel them, let's look separately and in more detail at workbook and worksheet events.

 

EVENT-HANDLING MACROS IN EXCEL VISUAL BASIC

Part one of a five-part series of blogs

Want to do something when a user opens a workbook, or stop them clicking on a particular cell? You need to learn how to attach code to Excel workbook or worksheet events!

  1. Introduction to Handling Events in Excel VBA Code (this article)
  2. Workbook Events
  3. Events for a Particular Worksheet
  4. Bypassing Macros
  5. Considerations for Other MS Office Application Events

This series of blogs is part of our Excel VBA online tutorial.  If you want to learn more, have a look at our classroom-based courses in VBA macros and/or Excel.

Comments on this blog

This blog has 2 comments:

Comment added by on 10 October 2012 at 18:19 GMT
Please, advice if there is a way to setup event-handler macro for the new created worksheet?
Reply from Andy Brown (blog author)

If you mean, is there a way to automatically create event-handling code for a newly inserted wroksheet, the answer is yes - like this.  It's not for the faint-hearted however!

 
Comment added by Andreas Killer on 25 November 2012 at 06:03 GMT
IMHO, to insert code in the code module of a sheet is a really bad example, especially for beginners. It requires that the security settings allows the access to the VBA code modules, which is not the default setting. And some versions of Excel can crash, if an event routine is inserted in the way that the mentioned website shows.

MS has specially built a method which is intended to create event routines:
Application.VBE.SelectedVBComponent.CodeModule.CreateEventProc

On top of everything you do not need it at all, there are event routines in the module "ThisWorkbook" that you can use. This is the way how professionals would do it.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'This event routine is called if a cell in any worksheet is changed
 
  'What is the name of  the sheet?
  If Sh.Name = "Sheet1" Then
    'This code runs only in Sheet1
    MsgBox Target.Address(0, 0) & " is changed"
  Else
    'This code runs in any other sheet
    'Which cell is changed?
    Select Case Target.Address(0, 0)
      Case "A1"
        'Note:
        '  If we change a cell here causes that this event is fired again!
        '  To prevent that: Events off
        Application.EnableEvents = False
        
        'Write something in B1
        'Note:
        '  You must refer to the given sheet object, because the sheet might not be the active sheet!
        '  If a usual macro changes a cell, this event routine is called also!
        Sh.Range("B1") = "something"
        
        'Don't forget to turn the events on.
        Application.EnableEvents = True
    End Select
  End If
End Sub

Reply from Andy Brown (blog author)

Thanks for this comment.  I have to say I don't agree with it, but in the interests of democracy have left it in, so that others can make up their own mind!

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.