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