There's a worksheet_activate event, too.

But be careful. If you swap to a different workbook, neither of these events
will fire.

You'll want to look at Workbook_WindowActivate and Workbook_WindowDeactivate (in
ThisWorkbook).





dgold82 wrote:

Wow! I leave the office for a couple hours and get tons to digest. Thanks to
all!

I am a real beginner with VBA and didn't realize until one of you mentioned
that there is a worksheet deactivate event. This is what ended up working
(the whole code):

Option Explicit
Private bStopTimer As Boolean

Sub StartTimer45min()
bStopTimer = True
Application.ScreenUpdating = True
Dim tmr As Long
bStopTimer = False
tmr = Timer
Range("k4,e4") = ""

Do
Range("k4").Value = Int(Timer - tmr)
DoEvents
If bStopTimer Then Exit Do
Loop Until Timer tmr + 2700

If bStopTimer Then
Range("k4").Value = 2700
Else
Range("e4") = "Time's Up!"
End If
End Sub

Sub quitTimer()
bStopTimer = True
End Sub

Private Sub Worksheet_Deactivate()
bStopTimer = True
End Sub

The deactivate code right above stops the timer when you change the
worksheet!! I would never have known without all your replies (which are
really fancy and for the most part over my head :-))

I am currently working on figuring out how to add a pause button to the code
above. If I figure that out I could change my deactivate code to the pause
code (instead of stop) and when a user comes back to the worksheet they can
just continue. I'm having a tough time figuring that out so help would be
appreciated in that attempt if you have time.

Thanks!

"Dave Peterson" wrote:

I'll buy into the fact you're smart--too smart to recommend using End <vvbg.

I think I'd stay with the way Chip shows.

Jim Thomlinson wrote:

I tried this and it sort of worked to halt code... On a sheet I added a
command button with the following code...

Dim lng As Long

For lng = 1 To 10000000
DoEvents
Application.StatusBar = lng
Next lng
Application.StatusBar = False

Then in the Deactivate event of thisworkbook I added
Private Sub Workbook_Deactivate()
End
End Sub

if you switch sheets with either
Ctrl+Tab
Window | Select another sheet
It executes the End and halts all code. Interestingly you can not just click
on the sheet in the Task Bar...

Of couse End is generally speaking a bad idea and I do not endorse the use
of this code but I wanted to prove that I'm smart... Best I could manage was
to sort of prove it so I guess I'm sorta smart. ;-)
--
HTH...

Jim Thomlinson

"Dave Peterson" wrote:

Take a look at Chip Pearson's notes:
http://www.cpearson.com/excel/OnTime.aspx

He includes notes on how to kill a pending (not running!) macro.

I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's
impossible--now that should challenge some smart people <vbg.)

dgold82 wrote:

Is there code that I can put in a worksheet module that automatically stops
all the running macros when a user leaves that worksheet? My problem is that
I have a timer macro that runs and if a user leaves that worksheet before it
completely counts down then the screen becomes all garbled until the macro is
stopped.

Thanks!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson