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