Excel Timer event

[Bruce007]

I know how to use OnTime and the Windows timers. I have a need to wake up a procedure in an Excel spread sheet every 1/2 hour and perform certain functions. The program that I am running into is that I have many Excel sheet open and when the timer expires and start my timer routine and the Excel sheet with the timer is not active then I get an error when I try and access cells on the sheets. A very simple application is that the sheet has two buttons: Start and Stop. Start starts the timer, and Stop stops it. Cell on Sheet1 display the time.

Dim stopflag

Public Sub StartButton()
stopflag=False
call DisplayTime

end Sub

Public Sub StopButton()
stopflag=True
end Sub

Public Sub DisplayTime()
Worksheet("Sheet1").range("D8").value = format(now(),"HH:MM:SS
if stopflag = False then call StartTimer()
end Sub

Public Sub StartTimer()
Application.OnTime Now() + TimeValue("00:00:01"), "DisplayTime"
End Sub

 

[GlennUK]

Have you tried this :

Public Sub DisplayTime()
Thisworkbook.Worksheet("Sheet1").range("D8").value = format(now(),"HH:MM:SS


that is, putting the "Thisworkbook" object qualifier before the sheet specifier.

Hope that helps.

Cheers, Glenn.

 

[Bruce007]

Thanks for the info. I will give it a try. I wish that the help files in VB were more detailed.