Is there any reliable way to detect when user switches worksheets?

My VBA needs to react when a user switches worksheets, and I have a set of
event catchers (Workbook_SheetSelectionChange, Worksheet_SelectionChange,
WorkSheet_Activate, WorkSheet_Deactivate) which I thought was foolproof.
Whether user switched sheets via my hyperlinks, used sheet tabs, or used the
Web toolbar, I got one of these events to trigger.

But user can start on sheet1, use Edit/GoTo to get to Sheet2, click an
option button, use Edit/GoTo to get back to sheet1, click a button all
without triggerring an event. I can trap more events with the option
buttons, but isn't there an easier way?

The problem with the hyperlink not firing the worksheet_activate was fixed in a
later version of excel (broken in xl2k, but works in xl2002, IIRC).

I'm not sure if that affected the web toolbar, though.



Nick wrote:

Is there any reliable way to detect when user switches worksheets?

My VBA needs to react when a user switches worksheets, and I have a set of
event catchers (Workbook_SheetSelectionChange, Worksheet_SelectionChange,
WorkSheet_Activate, WorkSheet_Deactivate) which I thought was foolproof.
Whether user switched sheets via my hyperlinks, used sheet tabs, or used the
Web toolbar, I got one of these events to trigger.

But user can start on sheet1, use Edit/GoTo to get to Sheet2, click an
option button, use Edit/GoTo to get back to sheet1, click a button all
without triggerring an event. I can trap more events with the option
buttons, but isn't there an easier way?

--

Dave Peterson

Using OnSheetActivate and OnSheetDeactivate works in XL2000.
(XL5 code)
In your workbook open event place something like this,
for each desired sheet ...

Sub FixItUp()
Me.Worksheets("Sheet3").OnSheetActivate = "DoThis"
Me.Worksheets("Sheet3").OnSheetDeactivate = "DoThat"
End Sub

In your workbook close event use code like this for every
sheet listed above ...
Sub UndoFix()
Me.Worksheets("Sheet3").OnSheetActivate = ""
Me.Worksheets("Sheet3").OnSheetDeactivate = ""
End Sub

DoThis and DoThat are the subs that run when the applicable
sheet is activated/deactivated. Place those subs in a general module.
Any of the sub names can be names you choose.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Nick"
wrote in message
Is there any reliable way to detect when user switches worksheets?

My VBA needs to react when a user switches worksheets, and I have a set of
event catchers (Workbook_SheetSelectionChange, Worksheet_SelectionChange,
WorkSheet_Activate, WorkSheet_Deactivate) which I thought was foolproof.
Whether user switched sheets via my hyperlinks, used sheet tabs, or used the
Web toolbar, I got one of these events to trigger.

But user can start on sheet1, use Edit/GoTo to get to Sheet2, click an
option button, use Edit/GoTo to get back to sheet1, click a button all
without triggerring an event. I can trap more events with the option
buttons, but isn't there an easier way?

Wow! This works with everything I've thrown at it -- changing sheets via
tabs, hyperlinks, Goto, web toolbar.

It's interesting that your solution works even when Workbook_SheetActivate
does not. I would have guessed they would respond to the same events. (I'm
testing under XL2000, since I don't know which versions my users have, so it
may have been fixed in XL2002.)


Thanks, much appreciated!

Nick


"Jim Cone" wrote:

Using OnSheetActivate and OnSheetDeactivate works in XL2000.
(XL5 code)
In your workbook open event place something like this,
for each desired sheet ...

Sub FixItUp()
Me.Worksheets("Sheet3").OnSheetActivate = "DoThis"
Me.Worksheets("Sheet3").OnSheetDeactivate = "DoThat"
End Sub

In your workbook close event use code like this for every
sheet listed above ...
Sub UndoFix()
Me.Worksheets("Sheet3").OnSheetActivate = ""
Me.Worksheets("Sheet3").OnSheetDeactivate = ""
End Sub

DoThis and DoThat are the subs that run when the applicable
sheet is activated/deactivated. Place those subs in a general module.
Any of the sub names can be names you choose.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Nick,
Appreciate the feedback.
As Microsoft "improves" each version problems emerge.
There are lots of improvements in the forthcoming xl 2007.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Nick"
wrote in message
Wow! This works with everything I've thrown at it -- changing sheets via
tabs, hyperlinks, Goto, web toolbar.

It's interesting that your solution works even when Workbook_SheetActivate
does not. I would have guessed they would respond to the same events. (I'm
testing under XL2000, since I don't know which versions my users have, so it
may have been fixed in XL2002.)
Thanks, much appreciated!
Nick


"Jim Cone" wrote:
Using OnSheetActivate and OnSheetDeactivate works in XL2000.
(XL5 code)
In your workbook open event place something like this,
for each desired sheet ...

Sub FixItUp()
Me.Worksheets("Sheet3").OnSheetActivate = "DoThis"
Me.Worksheets("Sheet3").OnSheetDeactivate = "DoThat"
End Sub

In your workbook close event use code like this for every
sheet listed above ...
Sub UndoFix()
Me.Worksheets("Sheet3").OnSheetActivate = ""
Me.Worksheets("Sheet3").OnSheetDeactivate = ""
End Sub

DoThis and DoThat are the subs that run when the applicable
sheet is activated/deactivated. Place those subs in a general module.
Any of the sub names can be names you choose.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Jim,

It's quite interesting to compare using the "OnSheetActivate" property that
you suggested with the Worksheet_Activate and Workbook_SheetActivate events.

As I wrote before, OnSheetActivate seems to trap the event no matter which
method the user employs to switch worksheets, even when neither
Worksheet_Activate nor Workbook_SheetActivate is raised.

Using your solution, I was able to eliminate several routines I had
previously used. In the process, I did note that "OnSheetActivate" does not
trap the worksheet change if the VB code itself issues a
Worksheet("Sheet2").Activate. However, in that case, the Worksheet_Activate
event is raised. Using OnSheetActivate, I no longer need to activate via
code, but it was a head-scratcher before I figured it out.

Do you know if the "OnSheetActivate" property remains valid in XL2007? It
seems like it's been removed from the documentation going back to XL2000.

Thanks again for your help.

Nick


"Jim Cone" wrote:

Nick,
Appreciate the feedback.
As Microsoft "improves" each version problems emerge.
There are lots of improvements in the forthcoming xl 2007.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware