Pop up

[Roger on Excel]

I have searched the message boards for advice on creating a pop up message
box, but unfortunately I have found the advice on the use of OnTime
confusing. For example, advice is not explicit on where to put various code
in the spreadsheet (e.g., module / This Workbook etc)

What I would like is to have a message box pop up every 15 mins from a
date/time that is stored somewhere in my spreadsheet or more preferably
within the code itself.

From that date/time i would like the message to then pop up every 2 minutes
when the spreadsheet is used.

people quote from the page http://www.cpearson.com/excel/OnTime.aspx,
however i find this site unhelpful on this matter as it assumes expertize on
where to place code.

Can anyone provide specific code together with instructions for where to
place the code such that the code works when the sheet is opened?

Thanks,

Roger

 

[Gary''s Student]

Public RunWhen As Double
Public Const cRunIntervalSeconds = 900 ' 15 minutes
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()
'
MsgBox ("fifteen minutes have once again passed")
'
StartTimer

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


Standard module macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select StartTimer
3. Touch RUN

 

[Roger on Excel]

Thanks "Gary's Student". - Very clear and concise instructions.\

I have two questions though :

1)How does one make the code run automatically from start of the sheet?
2)How does one enter a specific time and date from when the code activates -
for example after a 10 day trial period from when the sheet is first used -
todays date and time would be useful for experimenting with the code

Many thanks,

Roger

 

[Gary''s Student]

The first question is easy. Put a call to StartTimer in a WorkbookOpen event
macro.

The second is slightly more complex:

1. Pick an unused Document Property.
2. Have the WorkbookOpen macro check the property every time the doc is
opened.
3. If the property is not set, then:
a. set the property to the current date/time
b. IMMEDIATELY save the doc
4. If the property is set, then compared the saved value to the current
date/time to see if its time to start messaging.

 

[Roger on Excel]

Hi - thanks again.

what would i enter to start the StartTimer in the WorkbookOpen.Event?

Thanks,

Roger

 

[Gary''s Student]

Private Sub Workbook_Open()
Call StartTimer
End Sub

Let’s make sure its in the correct place.

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Roger on Excel]

Thanks for your help - very much appreciated.

All the best,

Roger

 

[Noel Mercado]

I tried to use the VBE for the pop up on a certain time you provided but i encoutered some problem, to close the pop up i need to click multiple times on "ok" and even if the workbook is already closed it open by itself with the pop up script.
I just changed the time to 120(2minutes) to test it.
please assist. Thank you.

Noel