On Jul 30, 4:07*pm, "Ronald R. Dodge, Jr."
wrote:
Yeah, you right. *However, he did mention of a timer macro, which is what
had me think of the OnTime command.

However, if the count down is a recurrsive looping, he could have a boolean
variable that is accessible to the code that is "running" as well as
accessible to the Worksheet's Code Module. *Within the Worksheet's Activate
Event, this boolean would be set to True, and have the worksheet's
Deactivate Event set this same boolean to False. *The procedure that is
supposedly running, each time it reaches a certain point within the
recursive code, it would check to see this boolean variable is false, and if
it is false, then the procedure would just end. *I know it's not a hard code
break out of the event, but it's a way around the situation to be done
programmatically.

If one is truly using OOP by using Class Modules along with setting up and
utilizing not only the properties and methods, but also the events, that can
get to be a bit more tricky and one would have to do a thorough
understanding how to setup the code to break out of it, though still use the
same basic concept as I stated above for procedure based coding.

OOP based coding is more difficult to learn than procedure based coding (Not
sure if there is really a industry proper term for this type of coding
that's not OOP based), but OOP based coding also a lot more powerful as you
can do more things with it. *Where OOP's biggest power comes from as opposed
to procedure based coding, it's in the use of events that procedure based
coding for the most part lacks. *Yes, mimicking can take place, but very
combersome to do.

One such example I have used within the userforms is the snippet code below:

* * Do Until l_bolCheck = False Or g_strCurrentReasonCode < ""
* * * * l_frmIssue.Show vbModeless
* * * * Do Until l_frmIssue.Visible = False
* * * * * * DoEvents
* * * * Loop
* * * * If g_strCurrentReasonCode = "" Then
* * * * * * If MsgBox(l_strMessage, vbCritical + vbYesNo, "Reporting Error")
= vbNo Then
* * * * * * * * l_bolCheck = False
* * * * * * End If
* * * * Else
* * * * * * m_objCurrentWorkOrder.fncRecordStatusReason l_StatusCode,
g_strCurrentReasonCode
* * * * * * pcdUpdateForm
* * * * End If
* * Loop

*--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000"Dave Peterson" wrote in message

...

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

Maybe this is too simple for what the OP is trying to accomplish--but
something like the code below seems like it would accomplish what he's
trying to do. It assumes that while his timer macro runs, it is not
changing the activesheet.

Sub testexit()

Dim t As Date
Dim s As String
Dim a As Integer

'what is the workbook and sheet name we're in now?
s = ActiveWorkbook.Name + "_" + ActiveSheet.Name

t = Timer

Do While Timer < t + 5

DoEvents

If ActiveWorkbook.Name + "_" + ActiveSheet.Name < s Then
a = 1
GoTo enditall
End If

Loop

enditall:
If a = 1 Then
MsgBox "Quit because you changed sheets or workbooks."
Else
MsgBox "Finished the timer loop"
End If

End Sub