ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Start a macro when an event occurs

It's possible to run macros when different events in Excel takes place. The following events can start a macro :

Event : Description / Example :
When the workbook opens Create a procedure and name it Auto_Open().
When the workbook closes Create a procedure and name it Auto_Close().
When a key is pressed Application.OnKey(Key, Procedure)
When Repeat is selected Application.OnRepeat(Text, Procedure)
When a time occurs Application. OnTime(EarliestTime, Procedure, LatestTime, Schedule)
When Undo is selected Application.OnUndo(Text, Procedure)
When a window is activated ThisWorkbook.Windows(1).OnWindow = "Procedure"
When a calculation is performed Application.OnCalculate="Procedure"
When the user double-clicks Application.OnDoubleClick="Procedure"
When a sheet is activated Application.OnSheetActivate="Procedure"
When a sheet is deactivated Application.OnSheetDeactivate="Procedure"
When new data is recieved Application.OnData="Procedure"

If you want to disable that a procedure is performed every time an event occurs, just apply en empty string to the event, e.g. : Application.OnData="".
The procedures Auto_Open and Auto_Close must be edited, deleted or renamed.

The following macros can be copied and pasted into an ordinary module sheet:

Sub Auto_Open()
' this macro will run every time the workbook is opened
    Worksheets(1).Activate ' active the preferred sheet
    Application.OnSheetActivate = "ProcedureName" 
    ' attach a procedure to the SheetActivate event
    MsgBox "Welcome!", vbInformation, "The time is " & _
        Format(Time, "hh:mm")
End Sub

Sub Auto_Close()
' this macro will run every time the workbook is closed
    Application.OnSheetActivate = "" 
    ' remove the procedure from the SheetActivate event
    MsgBox "Have a nice day!", vbInformation, _
        "The time is " & Format(Time, "hh:mm")
End Sub

You can only have one Auto_Open-procedure in each workbook. If you have more than one Auto_Open-procedures none of them will work. The same applies to Auto_Close-procedures.

In Excel97 you will find more event procedures attached to each sheet in the workbook and the workbook itself. These procedures can run macros before the user performs a doubleclick or before a rightclick, or when the user changes the cell selection.


Document last updated 1999-08-21 12:47:53      Printerfriendly version


Erlandsen Data Consulting   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address