Excel message box with timer

  Picklefactory 11:40 18 Mar 2008

Hello folks.
Might be a long shot....
I have a workbook that monitors order progress against delivery dates, I have some code that checks dates and if an item is late, auto emails relevant people, all OK so far, that works fine. I'd like to open the workbook automatically (Windows Task Scheduler, no problem there either), have it run the check and then save and close again, that is also pretty easy. My problem is, I need an option to be able to keep the file open if someone needs to work on it, so, if feasible, I'd like a message box to appear on workbook open, with a button to click that would skip the file save/close part of the code, and hence keep the file open to work on. If it could have a time of, say, 5 seconds for button to be clicked, and if not clicked, then continue with save and close.
Am I asking a lot?
Hope explanation is clear.

  Simsy 12:20 18 Mar 2008

this should not be much of a problem.

Have you written the existing code yourself? If so, you know more about VBA than I, and I'm not sure if I can help.

However, on the offchance that this may be of use, the following code will make a message box appear. Where it goes, and what subsequently needs to be added depends rather on the existing code;


Dim DoIStayOpen As Byte
DoIStayOpen = MsgBox("Do you want to keep working on this?", vbYesNo, "Continue Working")

If DoIStayOpen = vbYes Then
End If


Note that I readily defer to anyone who has another solution, or who can correct this, especially as I haven't included any of the "timing" aspect!



  Picklefactory 12:24 18 Mar 2008

Thanks Simsy, I'll have a play with that, and no, I've not really written the other code. I'm VBA newbie, but can just about manage to 'jiggle' 'em a bit to better suit my purpose, I can manage some very simple stuff, like the file save/close etc, but I get most either from here or Mr Excel. I'm trying to get my company to part with some cash to get some training on it, hmmmmm......... lets see.
Thanks again, I'll let you know how get on.

  Simsy 12:28 18 Mar 2008

I'm sure you'll be able to get it sorted. VoG will supply an answer, I'm sure, if you don't tick this as resolved soon!



  Picklefactory 12:44 18 Mar 2008

Oooh close, very close.
That gives me a Yes/No selection which works, but there could be quite a few of these, and don't want to have people needing to go click buttons un-neccessarily every few minutes, so I'm still holding out for simply 'Yes' button only to keep open or save/close after a few seconds. I'm intending on this sort of doing it's thing in the background, unless someone wants to use it specifically
Current code is

Sub auto_open()
Dim Cell As Object
' If the values in J14:J74 are greater than 1...
For Each Cell In Range("'CUTTING TOOLS'!J14:'CUTTING TOOLS'!J74")
If Cell > 1 Then

Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
.To = "[email protected]"
.CC = "[email protected]"
.Subject = "SUPPLIER OVERDUE NOTICE ON PROJECT " & ThisWorkbook.Name
.NoAging = True
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True
End If
Next Cell

Dim DoIStayOpen As Byte
DoIStayOpen = MsgBox("Do you want to keep working on this?", vbYesNo, "Continue Working")

If DoIStayOpen = vbYes Then

End If

End Sub


  VoG II 12:45 18 Mar 2008

As far as I know you can't dismiss a MsgBox automatically after a defined time. To do that you need something like this click here (easier) or you can use a modeless UserForm (harder).

  Picklefactory 12:47 18 Mar 2008

Yeah, I'm surprised you got in a reply before VoG™, maybe he's soaking up some rays, he is one who normally bails out my meagre efforts.

  Picklefactory 12:47 18 Mar 2008

Ooh caught me :-(

  Picklefactory 13:06 18 Mar 2008

Thanks VoG™, fancy that though, I'm struggling with that, I'm afraid. Copied/pasted the ktMsgBox code into VBA window, how do I get it to run to see what happens? Hate not knowing what I'm doing.

  Picklefactory 13:11 18 Mar 2008

As usual, managed to miss the completely obvious. Have just downloaded, will report back after a play.

  VoG II 13:18 18 Mar 2008

Ha! I'm at work and the download is blocked so I can't even try it :o(

However, reading the syntax, if one uses the timer feature it always returns VbOK so no good for your purpose.

I'll have a go at a modeless UserForm....

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?