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?