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

[Ratheesh]

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

 

[Martin Wheer]

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, procedure:=RunWhat, _
schedule:=True
SetFlag = True
.ScreenUpdating = oldAppScreenUpdate
End With
End If
End Sub

 

[Ratheesh]

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, procedure:=RunWhat, _
                schedule:=True
            SetFlag = True
            .ScreenUpdating = oldAppScreenUpdate
        End With
    End If
End Sub









- 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