Start a macro when an event occurs
1999-08-21 Events 0 65
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"|
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 SubYou 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.