Perform action upon workbook being reactivated

user_1319354 0
Viewed 139

I've got a workbook which has buttons which open other workbooks through VBA.

My understanding is that this initial workbook (lets call it the Hub workbook), is both ThisWorkbook and ActiveWorkbook when I'm in it.

Upon opening another workbook through one of the buttons, the newly opened workbook becomes ActiveWorkbook.

When I click back on to Hub, it becomes ActiveWorkbook once again.

I'd like to perform an action, only when Hub regains the status of being the active workbook.

I've tried doing this by performing an action on the worksheet, but this doesn't perform if it's the only sheet and I just click back on it from another workbook. It does if I have two worksheets for example and click back to this worksheet.

Private Sub Worksheet_Activate()
 MsgBox "Worksheet Reactivated"
End Sub

I can't seem to get it working as I require, but I'm sure there must be some way of doing this.

Any help would be appreciated.

Thanks

excel-2013 vba excel
1 Answers

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:

ThisWorkbook code pane dropdowns

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.

user_11885130
Related