In a large Excel spreadsheet, I am looking to add a simple timer which will,
when started, put say a 'Y' in a cell for 1 hour. No countdown display or
anything complicated needed.

I've googled and tried a couple from the web (VBA code) but they seem to tie
up the spreadsheet's resources (hourglass spinning etc) while they are
running, which is no good for what I want.

Any suggestions please?

TIA,

V

"Victor Delta" wrote:
In a large Excel spreadsheet, I am looking to add
a simple timer which will, when started, put say a
'Y' in a cell for 1 hour. No countdown display or anything complicated
needed.

Untested; there might be syntax errors.

Dim endtime as Double ' global to module

Sub startIt()
Range("A1") = "Y"
endTime = Now + TimeSerial(1,0,0) ' note: one-second resolution
Application.OnTime endTime, "stopIt"
End Sub

Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub

The OnError and OnTime/False statements allow stopIt to double as a way to
abort the time-out event before the 1-hour deadline, if you wish.

That is also the reason for putting the time-out deadline into a global
variable (endTime).

"joeu2004" wrote in message
...
"Victor Delta" wrote:
In a large Excel spreadsheet, I am looking to add
a simple timer which will, when started, put say a
'Y' in a cell for 1 hour. No countdown display or anything complicated
needed.

Untested; there might be syntax errors.

Dim endtime as Double ' global to module

Sub startIt()
Range("A1") = "Y"
endTime = Now + TimeSerial(1,0,0) ' note: one-second resolution
Application.OnTime endTime, "stopIt"
End Sub

Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub

The OnError and OnTime/False statements allow stopIt to double as a way to
abort the time-out event before the 1-hour deadline, if you wish.

That is also the reason for putting the time-out deadline into a global
variable (endTime).

Brilliant, that seems to do the job perfectly! Very many thanks.

Is there any way to force 'stopIt' if the spreadsheet is closed during the 1
hour?

V

"Victor Delta" wrote:
"joeu2004" wrote:
Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub
[....]
Is there any way to force 'stopIt' if the spreadsheet
is closed during the 1 hour?

You could create the following Workbook_Close event macro (untested; beware
of syntax errors):

Sub Workbook_Close()
stopIt
End Sub

The Workbook_Close event macro goes in the Workbook object, not in a
worksheet or normal module. To ensure proper syntax of the declaration,
click on the pull-down menus at the top of the VBA editing pane.

That paradigm presumes that stopIt is in a normal module (not a worksheet
object), and it is not Private.

Of course, there is no need to stop the timer. That should be done
automagically by Excel.

But I presume you want to clear A1 (in my example).

"joeu2004" wrote in message
...
"Victor Delta" wrote:
"joeu2004" wrote:
Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub
[....]
Is there any way to force 'stopIt' if the spreadsheet
is closed during the 1 hour?

You could create the following Workbook_Close event macro (untested;
beware of syntax errors):

Sub Workbook_Close()
stopIt
End Sub

The Workbook_Close event macro goes in the Workbook object, not in a
worksheet or normal module. To ensure proper syntax of the declaration,
click on the pull-down menus at the top of the VBA editing pane.

That paradigm presumes that stopIt is in a normal module (not a worksheet
object), and it is not Private.

Of course, there is no need to stop the timer. That should be done
automagically by Excel.

But I presume you want to clear A1 (in my example).

Thanks. Following your guidance on the pull-down menus, I've ended up with:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Run "stopIt"

End Sub

which seems to work well.

Previously I found that if you closed and then reopened the spreadsheet, the
timer was still running which was not what I wanted in that situation. (I've
also set up buttons to start and stop the timer.)

Thanks again,

V

"Victor Delta" wrote:
Following your guidance on the pull-down menus,
I've ended up with: Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "stopIt"
End Sub
which seems to work well.

You're probably right: BeforeClose instead of Close. I had not looked or
tested to see when each event occurs.

However, there really is no need to use Run "stopIt". It is inefficient.
Simply stopIt or Call stopIt should work just fine.


"Victor Delta" wrote:
Previously I found that if you closed and then reopened
the spreadsheet, the timer was still running which was
not what I wanted in that situation.

I wondered about that myself, and I intended to test it. Thanks for the
heads-up. I am a little surprised; but the operative word is "little" ;-).

"joeu2004" wrote in message
...
"Victor Delta" wrote:
Following your guidance on the pull-down menus,
I've ended up with: Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "stopIt"
End Sub
which seems to work well.

You're probably right: BeforeClose instead of Close. I had not looked or
tested to see when each event occurs.

However, there really is no need to use Run "stopIt". It is inefficient.
Simply stopIt or Call stopIt should work just fine.


"Victor Delta" wrote:
Previously I found that if you closed and then reopened
the spreadsheet, the timer was still running which was
not what I wanted in that situation.

I wondered about that myself, and I intended to test it. Thanks for the
heads-up. I am a little surprised; but the operative word is "little"
;-).

Amended it. Thanks.

V