Solved: Stopping a macro using a macro!

[edthehorse]

:dunno Hey, I've been hunting around looking for a way to stop a macro.. usually i would go into VBA and hit break or use CTRL-Break but i am setting up a sheet where the user wouldn't be used to using VBA and i would prefer to integrate something into a message box that would stop the macro or all macros by pressing no..
Basically code that would do the opposite to Call, VB has StopMacro as a function but it doesn't work in excel


any ideas?

Thanks in Advance

[Kenneth Hobs]

End can be used but some programmers don't like to use it. I would guess that your userform with an OK button would be non-modal. You can use an OnKey to interrupt macros too.

[edthehorse]

End can be used but some programmers don't like to use it. I would guess that your userform with an OK button would be non-modal. You can use an OnKey to interrupt macros too.

so I could use End Autocalc in one macro to end the autocalc macro already running?

ie

Sub test()
Dim Msg as Int
Msg= MsgBox ("yes or no",vbYesorNo,"tester")

If Msg=6
Call Autocalc
ElseIf Msg=7
End Autocalc
End if

End Sub

[snb]

Please describe what you want to do and what should be the purpose in a broader perspective.
The core of macros is their performing without any interference with what the user does.
The requirement to stop the macro is contrary to its main purpose. So why using a macro in the first place ?

[edthehorse]

Please describe what you want to do and what should be the purpose in a broader perspective.
The core of macros is their performing without any interference with what the user does.
The requirement to stop the macro is contrary to its main purpose. So why using a macro in the first place ?

Basically i use a macro to autocalc a sheet every 3 minutes, Sometimes the user will hit the start button that activates the autocalc more than once leading to multiple instances of the macro running. The macro itself is a simple application.ontime now + timevalue

The idea is that i have a dialog box(which currently works for turning on the macro) and i would like this macro to either call the autocalc macro on yes(as it currently does) or to stop the macro entirely on pressing no (which it doesnt currently do)

This is something i have looked to do in other macros too as not all users are familiar with VBA and sometimes i find its necessary to reset or break and that it would be helpful to have these basic tools attached to a button.

[Jan Karel Pieterse]

The trick is in having your macro keep score of how many times the user has pressed the button to start the timed process. That way, you can also limit it to 1 :-). For example:Option Explicit
Dim mdNextTime As Date
Public Sub StartTimer()
If mdNextTime <> 0 Then
'We already scheduled a time, do nothing
Else
mdNextTime = Now + TimeValue("00:03:00")
Application.OnTime mdNextTime, "TimedRoutine"
End If
End Sub
Public Sub TimedRoutine()
'Do stuff here
'Do more stuff
'Now schedule myself to be run again
mdNextTime = Now + TimeValue("00:03:00")
Application.OnTime mdNextTime, "TimedRoutine"
End Sub
Public Sub StopTimer()
If mdNextTime <> 0 Then
On Error Resume Next
'Unschedule next run
Application.OnTime mdNextTime, "TimedRoutine", , False
'Reset time
mdNextTime = 0
End If
End Sub

[snb]

....hit the start button that activates the autocalc more than ...

So why not making that button invisible as soon as the user hits it ?
And make it visible again after calculating ?

[edthehorse]

The trick is in having your macro keep score of how many times the user has pressed the button to start the timed process. That way, you can also limit it to 1 :-). For example:Option Explicit
Dim mdNextTime As Date
Public Sub StartTimer()
If mdNextTime <> 0 Then
'We already scheduled a time, do nothing
Else
mdNextTime = Now + TimeValue("00:03:00")
Application.OnTime mdNextTime, "TimedRoutine"
End If
End Sub
Public Sub TimedRoutine()
'Do stuff here
'Do more stuff
'Now schedule myself to be run again
mdNextTime = Now + TimeValue("00:03:00")
Application.OnTime mdNextTime, "TimedRoutine"
End Sub
Public Sub StopTimer()
If mdNextTime <> 0 Then
On Error Resume Next
'Unschedule next run
Application.OnTime mdNextTime, "TimedRoutine", , False
'Reset time
mdNextTime = 0
End If
End Sub


Hey Jan, thanks for getting back,


Tried that code but it has the same issue as before, if you hit the button the macro will run over and over, so say i hit it and then hit it again 2 secs later it will calculate the sheet at mdNexttime after each of these starts..

noone any idea on a kill macro code?!?!

[edthehorse]

So why not making that button invisible as soon as the user hits it ?
And make it visible again after calculating ?

i could hide the button but it doesnt need to be seen again in theory once the sheet is calculating every 3 minutes. if it comes back again there would still be the issue of it getting hit again and running in an offset parallel to the original

it sounds a lot more complicated than a simple 'stop calculating' should be

[snb]

it sounds a lot more complicated than a simple 'stop calculating' should be

To me too:


Private Sub Workbook_open()
sheets(1).oleobjects("button_cal").visible=true
End sub

Sub starttimer()
sheets(1).oleobjects("button_cal").visible=false
- - - - -
end sub


But you won't even need a button if you start the timer automatically when opening the workbook.

[edthehorse]

To me too:


Private Sub Workbook_open()
sheets(1).oleobjects("button_cal").visible=true
End sub

Sub starttimer()
sheets(1).oleobjects("button_cal").visible=false
- - - - -
end sub

But you won't even need a button if you start the timer automatically when opening the workbook.

tried the code but it doesn't seem to like it, "object not supported" was the error, are you sure you're using excel VBA friendly code?

also the idea behind starting it on a button is that other values are submitted into the sheet and the timer is then pressed at a certain time and left to run.

[snb]

It's even horsefriendly code. ;)

Since I do not wear my telepathy glasses I can't see the name of the button, nor of the sheet.
So if you adapt the string "button_cal" .....
And if necessary sheets(1) into sheet("sheetname")

Private Sub Workbook_open()
sheets(1).oleobjects("button_cal").visible=True
End Sub


or if only 1 activeX-button is in the sheet you might use:

Sheets(1).OLEObjects(1).visible = True



But if it's a formcontrol you will need:

Sheets(1).Buttons(1).visible = True

[edthehorse]

It's even horsefriendly code. ;)

Since I do not wear my telepathy glasses I can't see the name of the button, nor of the sheet.
So if you adapt the string "button_cal" .....
And if necessary sheets(1) into sheet("sheetname")

Private Sub Workbook_open()
sheets(1).oleobjects("button_cal").visible=True
End Sub

or if only 1 activeX-button is in the sheet you might use:

Sheets(1).OLEObjects(1).visible = True


But if it's a formcontrol you will need:

Sheets(1).Buttons(1).visible = True

Funny, nothing like a bit of encouragement!

I did change the string to the button and the sheet to the right name, but i hadnt realised that there was different references depending on activex and buttons(oleobject and buttons), and i also had the sub starttimer macro in a module instead of the worksheet and it wouldnt run, only gave the error, 'object doesn't support this property or method'

As i said in my first post i am still new to a lot of this and have a few gaping gaps in my knowledge!

[edthehorse]

meant to add thanks for the code,

Is there anyway to reset/break the code via button though?

[snb]

The more information you provide (a sample workbook e.g.) the better our advice can be....

[Jan Karel Pieterse]

Did you see I added a StopTimer routine? Tie that to a stop button so the user can click it to to cancel the scheduled calc.

My code ensures that only ONE recalc is scheduled, regardless how many times you click the Start button.
BTW: I'm no fan of hiding UI elements (like buttons) when they are not to be used, it is considered best practise to disable them instead.

[Paul_Hossler]

Another approach might be to use a non-modal UF with Start, Stop, and Exit command buttons

Standard module

Option Explicit
Public bRunning As Boolean
Public tRun As Date

Sub ReCalc()

'demo - statusbar every 5 sec
Application.StatusBar = "Launched at " & Format(Now, "hh:mm:ss")
tRun = Now + TimeSerial(0, 0, 5)
Call Application.OnTime(tRun, "ReCalc")

End Sub

Sub Main()
Load frmControl
Call frmControl.Show(vbModeless)
End Sub


UserForm:

Option Explicit
Private Sub cbExit_Click()

If bRunning Then Call cbStop_Click

Me.Hide
Unload Me

End Sub
Private Sub cbStart_Click()
tRun = Now + TimeSerial(0, 0, 5)

Call Application.OnTime(tRun, "ReCalc")
bRunning = True

Me.cbStart.Enabled = Not Me.cbStart.Enabled
Me.cbStop.Enabled = Not Me.cbStop.Enabled
End Sub

Private Sub cbStop_Click()
Application.OnTime EarliestTime:=tRun, Procedure:="ReCalc", Schedule:=False

bRunning = False

Me.cbStart.Enabled = Not Me.cbStart.Enabled
Me.cbStop.Enabled = Not Me.cbStop.Enabled

Application.StatusBar = False
End Sub
Private Sub UserForm_Initialize()
bRunning = False
Me.cbStart.Enabled = True
Me.cbStop.Enabled = False
End Sub


Paul

[edthehorse]

Did you see I added a StopTimer routine? Tie that to a stop button so the user can click it to to cancel the scheduled calc.

My code ensures that only ONE recalc is scheduled, regardless how many times you click the Start button.
BTW: I'm no fan of hiding UI elements (like buttons) when they are not to be used, it is considered best practise to disable them instead.


Thanks Jan, I tried using the code you gave me but i had the same issue as before, it keeps recalculating 4 secs after each time the button is pressed, i.e when i hit it and the hit it again 2 secs later it calculates every 2 secs...

hiding buttons is not the ideal solution alright, you need another macro to get the button back if theres any issue! looks like pauls solution below could work well.

[edthehorse]

Another approach might be to use a non-modal UF with Start, Stop, and Exit command buttons

Standard module

Option Explicit
Public bRunning As Boolean
Public tRun As Date

Sub ReCalc()

'demo - statusbar every 5 sec
Application.StatusBar = "Launched at " & Format(Now, "hh:mm:ss")
tRun = Now + TimeSerial(0, 0, 5)
Call Application.OnTime(tRun, "ReCalc")

End Sub

Sub Main()
Load frmControl
Call frmControl.Show(vbModeless)
End Sub

UserForm:

Option Explicit
Private Sub cbExit_Click()

If bRunning Then Call cbStop_Click

Me.Hide
Unload Me

End Sub
Private Sub cbStart_Click()
tRun = Now + TimeSerial(0, 0, 5)

Call Application.OnTime(tRun, "ReCalc")
bRunning = True

Me.cbStart.Enabled = Not Me.cbStart.Enabled
Me.cbStop.Enabled = Not Me.cbStop.Enabled
End Sub

Private Sub cbStop_Click()
Application.OnTime EarliestTime:=tRun, Procedure:="ReCalc", Schedule:=False

bRunning = False

Me.cbStart.Enabled = Not Me.cbStart.Enabled
Me.cbStop.Enabled = Not Me.cbStop.Enabled

Application.StatusBar = False
End Sub
Private Sub UserForm_Initialize()
bRunning = False
Me.cbStart.Enabled = True
Me.cbStop.Enabled = False
End Sub

Paul

Thanks paul that works perfectly for stopping the timer. So you need to define the values for application ontime if you want to be able to stop the macro?

I was wondering how to setup those user form controls... great steep learning curve on this forum!!

[Jan Karel Pieterse]

Did you tie your button to the StartTimer sub?

[Paul_Hossler]

So you need to define the values for application ontime if you want to be able to stop the macro?


The 'Stop' code in my example only prevents the demo ReCalc from being resubmitted, and removes a scheduled but un-run task.

Somewhere inside your real sub if its already running (probably inside a loop), you most likely need to test for bRunning = False and take appropriate action

Without details, I couldn't suggest anything else.

If you want, I can add some demo code to my earlier example

Paul

[edthehorse]

Did you tie your button to the StartTimer sub?

I tried tying each sub to a button but the only one that works is the timed routine, should i be pasting it into a module or the worksheet?

[Jan Karel Pieterse]

In a normal module.

[edthehorse]

The 'Stop' code in my example only prevents the demo ReCalc from being resubmitted, and removes a scheduled but un-run task.

Somewhere inside your real sub if its already running (probably inside a loop), you most likely need to test for bRunning = False and take appropriate action

Without details, I couldn't suggest anything else.

If you want, I can add some demo code to my earlier example

Paul

appreciate the help,

the attached sheet is a working example of one of the situations where i would like a break code button, its capturing data from the first sheet and printing it in consecutive rows on the next sheet with a timestamp. The problem is that in order to stop it running i need to hit break in VBA and wait for it to try and calculate, thats fine with 10 sec intervals but when its set to 5 you have to wait for it to come back around.

This was the reason i was looking for a reset button, basically a button that would break the code and reset so you could start again and possibly another button that would pause the code so that it would continue printing from where you stopped when you restart it.

It should be run on the 5 minutes exactly but at times someone will hit it too early or too late and i go into teh code and change the time to print the next one at the correct time and then reset it to print after 5 minutes again. So ideally the button would take this out of it and allow any user to reset and run again if they start too early/late.

any ideas?!

[Jan Karel Pieterse]

This is all you need:
Option Explicit
Dim I As Integer
Dim dNextTime As Double
Sub StartCapture()
If dNextTime <> 0 Then Exit Sub
dNextTime = Now + TimeValue("00:00:10")
Application.OnTime dNextTime, "Capture"
End Sub
Sub Capture()
'copy's cells from sheet one and pastes into sheet 2 on a timed interval
If I = 0 Then I = 1
Sheets(2).Cells(I + 1, 1) = Now()
Sheets(2).Cells(I + 1, 2) = Sheets(1).Range("B3")
Sheets(2).Cells(I + 1, 3) = Sheets(1).Range("B4")
Sheets(2).Cells(I + 1, 4) = Sheets(1).Range("B5")
Sheets(2).Cells(I + 1, 5) = Sheets(1).Range("B6")
I = I + 1
dNextTime = Now + TimeValue("00:00:10")
Application.OnTime dNextTime, "Capture"
End Sub
Sub CancelCapture()
Application.OnTime dNextTime, "Capture", , False
dNextTime = 0
End Sub



Tie your Start button to StartCapture.
Add a STop button tied to CancelCapture.

[Jan Karel Pieterse]

See attached.

NB: I also added a before_close event in thisworkbook to stop the capture when you close the file, otherwise Excel will open the file again once the time is come for a new ontime event.

[edthehorse]

See attached.

NB: I also added a before_close event in thisworkbook to stop the capture when you close the file, otherwise Excel will open the file again once the time is come for a new ontime event.

Thats Perfect Jan! exactly what i was looking for, thanks a million!

The before_close also solves that exact issue i had been having where i would need to exit the instance of excel to stop it running, solves a problem in a few sheets i'm using

Thanks to paul too for the help

I think i need to get better at asking the right questions here too!

[snb]

Just for illustration purposes..

[Paul_Hossler]

NB: I also added a before_close event in thisworkbook to stop the capture when you close the file, otherwise Excel will open the file again once the time is come for a new ontime event.

1. I forgot all about Excel remembering

2. Wondering if deactivating the WB by switching to another WB should stop the timer?

Paul

[russia-andro]

Basically i use a macro to autocalc a sheet every 3 minutes, Sometimes the user will hit the start button that activates the autocalc more than once leading to multiple instances of the macro running. The macro itself is a simple application.ontime now + timevalue

The idea is that i have a dialog box(which currently works for turning on the macro) and i would like this macro to either call the autocalc macro on yes(as it currently does) or to stop the macro entirely on pressing no (which it doesnt currently do)

This is something i have looked to do in other macros too as not all users are familiar with VBA and sometimes i find its necessary to reset or break and that it would be helpful to have these basic tools attached to a button.
+1

[snb]

I made a simpler version than I posted before to do this.