On activating a Workbook . . .

Topic

New Reply

[WSMartinM]

I know how to run a procedure when a Worksheet is activated . . .

in the Worksheet code:

Private Sub Worksheet_Activate()

        [Some code]

End Sub

But how can I run a procedure when a Workbook is activated ?

I would also like to run a procedure when the Workbook is de-activated (loses focus) but I am not sure that’s possible – I can cope without that.

Thanks, Martin

• This topic was modified 1 month, 2 weeks ago by WSMartinM.

Reply | Quote

Replies

PS I tried the following, in the Workbook code, but it did nothing at all !

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)

    [Some code]

End Sub

Reply | Quote

PS I tried the following, in the Workbook code, but it did nothing at all !

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)

[and  Private Sub Workbook_WindowActivate(ByVal Wn As Window) ]

    [Some code]

End Sub

Reply | Quote

In ThisWorkbook (under “Microsoft Excel Object”, not in a macro code module) use

Private Sub Workbook_Activate()
  <...your code goes here...>
End Sub

Reply | Quote

Yes, that works, thanks, but only when opening the Workbook, not when, for instance, switching from another Workbook.

I could use the Auto-Open macro if I wanted it to run on opening.

What am I missing ?

Reply | Quote

“Private Sub Workbook_Open()” will run once, when the workbook is opened.

“Private Sub Workbook_Activate()” will run every time the workbook gains focus, for example if you switch from a different workbook/application back to the one that contains that code.

This link (and the ones below it) show all the events that you can do in the workbook: https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.activate(even)

Reply | Quote

<p style=”text-align: left;”>Thanks – sorted now.
For some reason Private Sub Workbook_Activate() doesn’t run when I switch from one Workbook to another, but Private Sub Workbook_WindowActivate(ByVal Wn As Window) does run.</p>

Reply | Quote