Thank you

I appreciate the time you take to help so many of us.

"Chip Pearson" wrote in message
...

Add a module level variable (before and outside of any procedure)
named Cancel As Boolean and then modify your code to something like


Do While Timer < Start + TotalTimeInMinutes
DoEvents
If Cancel = True Then
' other clean up code
Exit Do
End If
Loop


Then, assign the following code to the command button that is to
cancel the timer:

Public btnCancel_Click()
Cancel = True
End Sub


As Dave Hawley wrote, you can allow the user to use ESC or CTRL BREAK
to break out of the loop. You can incorporate such code with the code
above to get something like


On Error GoTo BreakHander
Application.EnableCancelKey = xlErrorHandler
Do While Timer < Start + TotalTimeInMinutes
DoEvents
If Cancel = True Then
' other clean up code
Exit Do
End If
Loop
' the rest of your code
Exit Sub
BreakHandler:
If Err.Number = 18 Then
' user broke out of loop
Cancel = True
Resume Next
Else
' some other error
End If

The EnableCancelKey setting tells VBA to raise an error 18 when the
user breaks hits break. The error handler block sets the Cancel
variable to True and that will break out of the loop. As an aside, it
is possible to set EnableCancelKey to completely disable the break
key. Use that setting with extreme caution because if your code goes
into an infinitely loop, the only way to get out is CTRL ALT DELETE to
total kill off the Excel application and lose all your unsaved
changes.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Sun, 16 May 2010 21:24:39 -0700, "ordnance1"
wrote:

I found this code below on-line (can not remember where) but just
wondering
if someone could tell be how to stop the timer? I want the user to have
the
option to stop the timer if necessary.

Option Explicit
Sub Auto_Open()
'
' AutoRun Macro
' Macro recorded 2/3/2001 by Patrick C. Simonds
'

bSELCTIONCHANGE = False

Events.Enable_Events


Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 15 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
'msgbox "This file has been open for " & TotalTime / 60 & " minutes.
You
have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
'msgbox "Excel will now close."


ThisWorkbook.Close True

End Sub