Stop all macros in worksheet when left

[dgold82]

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!

 

[Ronald R. Dodge, Jr.]

I'm assuming you have the time portion set to a variable that is accessible
to the Worksheet's Code Module.

You have one procedure that schedules the next time another procedure within
the module is ran. You then set the Deactivate Event within the Worksheet's
Code Module ("Worksheet" in the left combo-box and "Deactivate" in the right
combo-box just above the code module) to run the command that unschedules
the next run time.

Example code line within the Deactivate Event

Application.OnTime m_dteNextRunTime, "pcdReoccuringProcedure", , False

To get it back going again when the worksheet becomes active again, you just
use the Activate Event, set the Date/Time variable accordingly, and then use
the OnTime method to get it a going again.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Dave Peterson]

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>.)

 

[Ronald R. Dodge, Jr.]

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

 

[Jim Thomlinson]

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. ;-)

 

[Chip Gorman]

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, andif
it is false, then the procedure would just end.  I know it's not a hardcode
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

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

 

[Ronald R. Dodge, Jr.]

You are right about the End bit. If anything, I use Exit <KeyWord> like in
this case, Exit Sub. However, even that has to be used in a proper
structure.

The angle I was thinking, your Deactivate Event would have the following
statement:

m_bolWorksheetActive = False


Then within your For...Next block, put in the following code:

DoEvents
If m_bolWorksheetActive Then
Application.StatsBar = lng
Else
'If you want, you can put in some other command as to the reason for
exiting the For block early
'after this line, and before the Exit For line.
Exit For
End If

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Dave Peterson]

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.

 

[dgold82]

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]

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).

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!