Excel Macro 4.0 event handling

2
votes

Following these steps, you can create an Excel 4.0 macro within a workbook:

  1. Right click on a sheet/tab (e.g. Sheet1) at the bottom of the Excel workbook.
  2. Click Insert....
  3. Select MS Excel 4.0 Macro and hit OK.

You get a new sheet "Macro1" with macros enabled.

How do you handle events like SheetSelectionChangeEvent for this sheet in code?

I am able to subscribe to and handle these events for regular sheets (Sheet1, Sheet2, etc.), but for the Macro1 sheet, these regular events aren't fired. Any ideas?

1
excelmacrosevent-handlingexcel-4.0

1 Answers

2
votes

Reference is available from here: http://support.microsoft.com/kb/128185

ON functions allow you to specify a macro to be run when a certain event occurs. The ON functions turn on and off this special event handling. ON functions are turned on by specifying the type of event to wait for, such as recalculation, a specific time, or a key to be pressed, and the macro to be run when the event occurs. ON functions are turned off by using the same formula but omitting the argument specifying the macro to be run.

ON.DATA         Runs a macro when data is entered
ON.DOUBLECLICK  Runs a macro when you double-click any cell or object
                  on the specified document or double-click any item
                  on the specified chart
ON.ENTRY        Runs a macro when a document is recalculated
ON.KEY          Runs a macro when a specified key is pressed
ON.RECALC       Runs a macro when a document is recalculated
ON.SHEET        Runs a macro when a specified sheet is selected
ON.TIME         Runs a macro at a specific time
ON.WINDOW       Runs a macro when you switch to a window