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.

Example:
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