cant stop OnTime initiated macro, etc
 

Nathan,

Is your workbook_open event code in ThisWorkbook code module. It needs to be
there not in a standard code module.

It won't stop, as OnTime is part of the Application object, so continues
when the workbook closes, and will continue so until Excel ends or it is
stopped. To stop it, you need to save the details when you start it, and
re-issue exactly the same command with Schedule = False.

Chip Pearson has a good explanation at
http://www.cpearson.com/excel/ontime.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"nathan" wrote in message
...
I am having two problems with a macro that i want to run continuously when
the workbook it resides in is opened:

1) it will not run automatically when the workbook is opened as I want it
to. i have to start it manually.
2) it will not stop when the workbook is closed. it keeps running as long
as excel is open and relaunches the workbook on the stated schedule. I
don't want it to run when the workbook is closed.

Below is the macro, thank you!:

Private Sub workbook_open()
'starts when the workbook is opened and schedules the leverage macro
procedure to run every minute
Application.OnTime Now() + TimeValue("00:01:00"), "leverage"

End Sub

Sub leverage()
a = ThisWorkbook.Name
If Workbooks(a).Worksheets("a.book").Range("a7").Valu e < 10 Then
workbook_open
Else: Dim msg, style, title, response
msg = "Leverage is 10 or greater!!"
style = vbOKOnly + vbExclamation + vbCritical +
vbDefaultButton1 + vbApplicationModal + vbMsgBoxSetForeground
title = "WARNING!!"
response = MsgBox(msg, style, title)
workbook_open
End If
End Sub

cant stop OnTime initiated macro, etc
 

well i looked at chip pearson's explanation and tried to set up what he suggested. but what I coded won't stop the macro. Here is what I did, all in the same module, anyone have any suggestions?

'this stores the exact time the macro is scheduled so that it can be stoped when the workbook is close
Public RunWhen As Doubl
Public Const cRunIntervalSeconds = 60 ' one minut
Public Const cRunWhat = "leverage

Sub StartTimer(

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat,
schedule:=Tru
End Su

Sub leverage(
a = ThisWorkbook.Nam
If Workbooks(a).Worksheets("a.book").Range("a7").Valu e < 10 The
StartTime
Else: Dim msg, style, title, respons
msg = "Leverage is 10 or greater!!
style = vbOKOnly + vbExclamation + vbCritical + vbDefaultButton1 + vbApplicationModal + vbMsgBoxSetForegroun
title = "WARNING!!
response = MsgBox(msg, style, title
StartTime
End I
End Su

Sub stoptimer(

On Error Resume Nex
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, schedule:=Fals

End Su

Private Sub workbook_beforeclose(cancel As Boolean

Run (stoptimer

End Su

cant stop OnTime initiated macro, etc
 

Where are all of the procedures located?

BeforeClose should be in ThisWorkbook, and either all should be in
ThisWorkbook, or preferably, the Public variables, StartTimer and STopTimer
should be in a standard code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"nathan" wrote in message
...
well i looked at chip pearson's explanation and tried to set up what he
suggested. but what I coded won't stop the macro. Here is what I did, all
in the same module, anyone have any suggestions?:

'this stores the exact time the macro is scheduled so that it can be
stoped when the workbook is closed
Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' one minute
Public Const cRunWhat = "leverage"


Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub

Sub leverage()
a = ThisWorkbook.Name
If Workbooks(a).Worksheets("a.book").Range("a7").Valu e < 10 Then
StartTimer
Else: Dim msg, style, title, response
msg = "Leverage is 10 or greater!!"
style = vbOKOnly + vbExclamation + vbCritical +
vbDefaultButton1 + vbApplicationModal + vbMsgBoxSetForeground
title = "WARNING!!"
response = MsgBox(msg, style, title)
StartTimer
End If
End Sub

Sub stoptimer()

On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat,
schedule:=False

End Sub


Private Sub workbook_beforeclose(cancel As Boolean)

Run (stoptimer)

End Sub

cant stop OnTime initiated macro, etc
 

Forgive me for not having much experience with coding and the different types of modules. If I am understanding your question correctly, all of the code (procedures) is located in the same module which is in ThisWorkbook. As far as I can tell the module is a standard module (as opposed to a class module). Should some of the code be in separate modules

FYI - I am using ThisWorkbook.name because the workbook is constantly being saved under a new name. I want the procedures to work in the new named workbooks and this way I don't have to change the code to reflect the new name. That's the idea anyway

thanks for your help.

cant stop OnTime initiated macro, etc
 

Nathan,

ThisWorkbook is a class module, a very special class module. With class
modules, code is only accessible via a class identifier.

As I suggested, some code is best placed there, some is better in a standard
code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"nathan" wrote in message
...
Forgive me for not having much experience with coding and the different
types of modules. If I am understanding your question correctly, all of the
code (procedures) is located in the same module which is in ThisWorkbook.
As far as I can tell the module is a standard module (as opposed to a class
module). Should some of the code be in separate modules?

FYI - I am using ThisWorkbook.name because the workbook is constantly
being saved under a new name. I want the procedures to work in the new
named workbooks and this way I don't have to change the code to reflect the
new name. That's the idea anyway.

thanks for your help.