Stop vbTimer

[Cajeto 63]

Hello Everybody,

I have copied a code for a timer in a post and applied it to my workbook to
automatically show a userform after a short period of time.
My big problem is that when I close the workbook without quitting Excel the
timer continue running and re-open my workbook after the preset period to
show the userform again.
Is there a way to have the timer stopped when closing the workbook?

My code bellow:

In Module:

Option Explicit

Public nSaveWB As Date

Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 0, 5) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub

Public Sub SaveWB()
Alerte.Show
End Sub


In ThisWorkbook:

Private Sub workbook_open()
Alerte.Show
End Sub


In Userform: (called Alerte)

Private Sub CommandButton1_Click()
Alerte.Hide
Call SetSaveWBTimer
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call SetSaveWBTimer
End If
End Sub


Thanks for your help.

 

[Ozgur Pars]

Hi Cajeto63,
I use the below code to show the time on my userform. In the terminate event
of the userform I call Stoptimer so that I don't get the same problem... (you
might put it in the workbook beforeclose event)

Hope it helps,
Ozgur


Public RunWhen As Double
Public Const cRunIntervalSeconds = 60
Public Const cRunWhat = "The_Sub"

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


Sub The_Sub()
UserForm2.Label475.Caption = Format(Now, "dd mmm yy hh:mm")
UserForm2.Label476.Caption = Format(Now, "dd mmm yy hh:mm")
UserForm2.Label514.Caption = Format(Now, "dd mmm yy hh:mm")
Call StartTimer
End Sub


Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat,
schedule:=False
End Sub

 

[Cajeto 63]

Hi Ozgur,
Thank you very much for you help.

I replaced the code I was using with yours and it partially works but I get
another problem.
When I run the sub StopTimer by hand from the VB windows it stops showing
the userform which is a very good point.

But VB does not want me to use the Workbook_BeforeClose event. It gives me a
compile error saying:"Procedure declaration does not match description of
event or procedure having the same name" and I'm not sure I understand what
it means.
Could it come from the fact that I already have a Workbook_open event in the
workbook?

And if so how can I get rid of the problem?

--


Regards, Cajeto 63.

 

[Ozgur Pars]

Cajeto,
I tried it out now and I have no problems with the before_close event.
Maybe you can post your code.

Just to set the record straight this is not my code(I wish) I found it on
the net...

Ozgur

 

[Cajeto 63]

Ozgur,
I pasted the code I have in ThisWorkbook bellow. Thanks again for your help.

Private Sub Workbook_Open()
Alerte.Show
End Sub

Private Sub workbook_BeforeClose()
Call StopTimer
End Sub
--


Regards, Cajeto 63.

 

[Art H]

When I open the code for ThisWorkbook and then click on Workbook in the
first pulldown then click on BeforeClose in the second pulldown, the
interface I get for Excel 2000 is

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Art

 

[NickHK]

As Art pointed out, you need to use the exact declarations that Excel
expects; you cannot make up you own version.
So, it is easier to get Excel to generate them for you, by clicking those 2
drop downs and selecting the various object and events.

NickHK

 

[Cajeto 63]

Thank you all for your helpfull help, it works perfectly now.


--


Regards, Cajeto 63.