A Worksheet
module such as, say, Sheet1
, is a special type of class module that inherits members of the Worksheet
class, and lets you readily handle events defined for a Worksheet
object. At run-time, that Sheet1
module represents a very specific instance of the Worksheet
class - so any event handlers you implement in that module, are only ever invoked for events fired from that object.
Thus, a Worksheet_Activate
handler in the code-behind of Sheet1
will only ever run when the Sheet1
object fires its Activate
event.
If you don't care for any specific or particular worksheet, then you need to handle events at a different level: the ThisWorkbook
module is a special type of class module that inherits the members of the Workbook
class, and lets you readily handle events defined for a Workbook
object. This workbook object always represents the specific workbook that hosts the VBA project you're in, and no other workbook.
A Workbook
object fires a SheetActivate
event whenever any sheet (be it a Worksheet
, a Chart
sheet, or any other type of sheet) is activated - you get the activated sheet through the Sh
parameter of the corresponding handler.
A Workbook
object also fires its own Activate
event whenever it becomes the ActiveWorkbook
.
Looks like you want to handle a Workbook
event. Experiment with the handlers you can get in the ThisWorkbook
module, see what best fits your requirements.
Select Workbook
from the combobox at the top-left of the code pane in ThisWorkbook
, then select an event from the right-hand side combobox, to get the VBE to automatically generate an event handler prototype with the correct signature:
If you need to handle Application
-wide events, things get a little bit more involved, but the crux of it is that you need to have a class module (could be ThisWorkbook
) that declares a WithEvents
variable:
Private WithEvents App As Excel.Application
And now the top-left dropdown will list App
, and when that's selected the top-right dropdown will let you pick an Excel.Application
event to handle... provided that you Set
this App
object variable to a valid Excel.Application
reference, say, in the Workbook_Open
handler:
Private Sub Workbook_Open()
Set App = Me.Application
End Sub
Now you can handle an event that's fired when any workbook is activated, for example.
Workbook.Activate
and the event mentioned. The event "occurs when a workbook, worksheet, chart sheet, or embedded chart is activated." – BigBen