Application.Ontime for Private Sub(Proceedure) - ThisWorkbook*
 

Hi All,

Can anyone help me for the below query:
I want to pop up a msgbox in every 1 minute(executing the same
proceedure when workbook open, but it should continue after every
minute)

eg:

Private Sub msg_box()
msgbox "Please close the file"
Application.OnTime Now + TimeValue("00:00:0"), "msg_box" 'here I am
not able to use this proceedure
End Sub

In module we can use this, but without calling the proceedure from
module how can I run this from Private Sub itself?

Thanks a lot in advance
Ratheesh

Application.Ontime for Private Sub(Proceedure) - ThisWorkbook*
 

Hi Ratheesh,

Below is code I use to run Ontime. It inturn calls another sub,
"QuerySheets" which updates my spreadsheets.

Martin


Public Sub OnTrack() 'sets time and does the OnTime thing
Dim oldAppScreenUpdate As Boolean
Dim RunWhen As Date
Dim RunWhat As String
On Error Resume Next
If Not SetFlag Then 'flag is to prevent repetitions
With Application
oldAppScreenUpdate = .ScreenUpdating
.ScreenUpdating = False
RunWhen = TimeSerial(Hour(Time), Minute(Time) + 1, 0)
RunWhat = "QuerySheets"
.OnTime earliesttime:=RunWhen, procedu=RunWhat, _
schedule:=True
SetFlag = True
.ScreenUpdating = oldAppScreenUpdate
End With
End If
End Sub

"Ratheesh" wrote in message
...
Hi All,

Can anyone help me for the below query:
I want to pop up a msgbox in every 1 minute(executing the same
proceedure when workbook open, but it should continue after every
minute)

eg:

Private Sub msg_box()
msgbox "Please close the file"
Application.OnTime Now + TimeValue("00:00:0"), "msg_box" 'here I am
not able to use this proceedure
End Sub

In module we can use this, but without calling the proceedure from
module how can I run this from Private Sub itself?

Thanks a lot in advance
Ratheesh

Application.Ontime for Private Sub(Proceedure) - ThisWorkbook*
 

On Aug 7, 9:15*am, "Martin Wheer" wrote:
Hi Ratheesh,

Below is code I use to run Ontime. *It inturn calls another sub,
"QuerySheets" *which updates my spreadsheets.

Martin

Public Sub OnTrack() * *'sets time and does the OnTime thing
* * Dim oldAppScreenUpdate As Boolean
* * Dim RunWhen As Date
* * Dim RunWhat As String
* * On Error Resume Next
* * If Not SetFlag Then 'flag is to prevent repetitions
* * * * With Application
* * * * * * oldAppScreenUpdate = .ScreenUpdating
* * * * * * .ScreenUpdating = False
* * * * * * RunWhen = TimeSerial(Hour(Time), Minute(Time) + 1, 0)
* * * * * * RunWhat = "QuerySheets"
* * * * * * .OnTime earliesttime:=RunWhen, procedu=RunWhat, _
* * * * * * * * schedule:=True
* * * * * * SetFlag = True
* * * * * * .ScreenUpdating = oldAppScreenUpdate
* * * * End With
* * End If
End Sub

"Ratheesh" wrote in message

...



Hi All,

Can anyone help me for the below query:
I want to pop up a msgbox in every 1 minute(executing the same
proceedure when workbook open, but it should continue after every
minute)

eg:

Private Sub msg_box()
msgbox "Please close the file"
Application.OnTime Now + TimeValue("00:00:0"), "msg_box" *'here I am
not able to use this proceedure
End Sub

In module we can use this, but without calling the proceedure from
module how can I run this from Private Sub itself?

Thanks a lot in advance
Ratheesh- Hide quoted text -

- Show quoted text -

Hi Martin,

Thanks a lot for your help...
Actually I got some more things from your code than what I really
asked for...

Cheers
Ratheesh