Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User gunnersboy
(fountain of knowledge) Mon 20-Apr-15 19:11:42
Print Post

Excel Message Box


[link to this post]
 
I'm trying to create a message box that pops up after a predefined time. 9am/1pm/5pm/9pm so after each of those times have passed it displays a message in excel.

I can only seem to work out how to display a message when the document is opened using VBA. Has anyone got any knowledge on how to do this for time dependent messages?

Any help is greatly appreciated.

Virgin Media:120Mb/s

Download Speed: 123.8 Mb/s
Upload Speed: 10.0 Mb/s
My Broadband Speed Test
Standard User micksharpe
(legend) Mon 20-Apr-15 20:02:02
Print Post

Re: Excel Message Box


[re: gunnersboy] [link to this post]
 
You may be able to use the Application.OnTime method to achieve what you want.

Faced with the choice between changing one’s mind and proving that there is no need to do so,
almost everyone gets busy on the proof. -- J.K. Galbraith
Standard User gunnersboy
(fountain of knowledge) Mon 20-Apr-15 20:17:55
Print Post

Re: Excel Message Box


[re: micksharpe] [link to this post]
 
I've had a read. It sounds like that's what I want but have no idea how to implement. Can you help? Basically if it surpasses 9am it needs to display a message and likewise for 1pm, 5pm and 9pm all with different messages.

Thanks.

Virgin Media:120Mb/s

Download Speed: 123.8 Mb/s
Upload Speed: 10.0 Mb/s
My Broadband Speed Test


Register (or login) on our website and you will not see this ad.

Standard User micksharpe
(legend) Mon 20-Apr-15 21:20:20
Print Post

Re: Excel Message Box


[re: gunnersboy] [link to this post]
 
I don't have a lot of time to spare (excuse the pun) and your requirements are a little vague. Also, I haven't attempted this sort of stuff with Excel. Sorry to bounce you but you may be better off posting your question in stackoverflow, but you'll need to be pretty clear about your requirements. Some things to consider:
  • Is this for your own use or other people's? If the latter, how will you distribute it?
  • Must the events fire only when a specific Excel workbook is open (and will it be left opened)?
  • Must the events start Excel if it is not running? In which case a VB app running in the system tray might be more appropriate.
  • Will the events need to be re-queued when they fire?
  • Would it be more appropriate to use the Windows Task Scheduler?
  • and so on...
It seems to be a strange thing to want to do inside an Excel workbook, although MS have provided a way to make it happen.

Faced with the choice between changing one’s mind and proving that there is no need to do so,
almost everyone gets busy on the proof. -- J.K. Galbraith
Standard User gunnersboy
(fountain of knowledge) Mon 20-Apr-15 23:02:33
Print Post

Re: Excel Message Box


[re: micksharpe] [link to this post]
 
OK. I've changed route a bit. I have some code which looks like this:

Private Sub Workbook_Open()
MsgBox "CONTENT OF MESSAGE", vbQuestion, "TITLE"
End Sub

How do I get it to repeat that pop up message every 4 hours?

This will hopefully be a bit easier to answer but I think I've gone round and round now.

Thanks.

Virgin Media:120Mb/s

Download Speed: 123.8 Mb/s
Upload Speed: 10.0 Mb/s
My Broadband Speed Test
Standard User micksharpe
(legend) Mon 20-Apr-15 23:47:19
Print Post

Re: Excel Message Box


[re: gunnersboy] [link to this post]
 
This code will display a message every 15 seconds:

Visual Basic
1
23
45
67
89
1011
1213
1415
Sub Workbook_Open()
    Call Schedule    MsgBox "Workbook_Open"
End Sub 
Sub Schedule()    Application.OnTime _
        EarliestTime:=Now + TimeValue("00:00:15"), _        Procedure:="ThisWorkbook.Message"
End Sub 
Sub Message()    Call Schedule
    MsgBox "Message triggered"End Sub

The code needs to be placed in the 'ThisWorkbook' object (not a module). You will have fun trying to edit the code once it is running. smile

Faced with the choice between changing one’s mind and proving that there is no need to do so,
almost everyone gets busy on the proof. -- J.K. Galbraith
Standard User cheshire_man
(knowledge is power) Wed 22-Apr-15 07:39:57
Print Post

Re: Excel Message Box


[re: micksharpe] [link to this post]
 
In reply to a post by micksharpe:
...You will have fun trying to edit the code once it is running. smile
Start Excel in Safe Mode?

Tony
We have more and more laws, and less and less enforcement
  Print Thread

Jump to