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

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" <(E-Mail Removed)> wrote in message
news:2f9a8475-040a-4a23-83cd-(E-Mail Removed)...
> 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

On Aug 7, 9:15*am, "Martin Wheer" <madw0...@ihug.co.nz> 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, procedure:=RunWhat, _
> * * * * * * * * schedule:=True
> * * * * * * SetFlag = True
> * * * * * * .ScreenUpdating = oldAppScreenUpdate
> * * * * End With
> * * End If
> End Sub
>
> "Ratheesh" <ratheesh.ramachandh...@gmail.com> wrote in message
>
> news:2f9a8475-040a-4a23-83cd-(E-Mail Removed)...
>
>
>
> > 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