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