Solved: Detecting when a different sheet is made active without using Worksheet_Activate

[xltrader100]

I have a form that loads a different picture depending on what sheet is active. I'm using the Worksheet_Activate event to switch the pictures, and this works fine if I change sheets by clicking on the Sheet Tab. However it doesn't work if I change sheets by just clicking on an inactive sheet to bring it to the front, as this apparently doesn't trigger the Activate Event.

Is there any way I can detect when the active sheet changes without depending on the Activate Event?

[Aflatoon]

What do you mean by "clicking on an inactive sheet to bring it to the front"? Do you mean switching to a different workbook? If so, you need the workbook_activate event or, more likely, an application-level application_workbookactivate event

[xltrader100]

All the sheets are in the same workbook, with multiple sheets visible in different Windows. When I click on a sheet in the background, it's Window comes to the front and it becomes active, except there was no Sheet_Activate Event issued. I need to change sheets by clicking on the Sheet Tab before I get an Activate Event.

[Aflatoon]

Sounds like a workbook_windowactivate event then.

[xltrader100]

Exactly so! That fixed it. Thank-you.

[xltrader100]

Just to follow up on this, it turns out that the Workbook_WindowActivate Event doesn't issue when the active sheet is changed by a click on the Sheet Tabs, because then the new sheet is coming up in the same window. So in order to make sure my code responds to both methods of changing sheets, I have to account for both events. These 2 subs illustrate the way sheet activation events work.

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Debug.Print "Active Sheet name = " & ActiveSheet.Name & " after click on inactive window"
End Sub
No Worksheet_Activate Event happens when the sheet changes in different windows.

Private Sub Worksheet_Activate()
Debug.Print "Active Sheet name = " & ActiveSheet.Name & " after click on Sheet Tab"
End Sub No Workbook_WindowActivate Event happens when the sheet changes in the same window.