Start a macro when an event occurs

 1999-08-21    Events    0    62

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.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.