Excel message box with timer

  Picklefactory 11:40 18 Mar 08
Locked

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.
Thanks

  Simsy 12:20 18 Mar 08

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
?????
Else
????
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!

Regards,

Simsy

  Picklefactory 12:24 18 Mar 08

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 08

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!

Regards,

Simsy

  Picklefactory 12:44 18 Mar 08

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
.Body = "A SUPPLIER IS OVERDUE ON DELIVERY ON THE ABOVE PROJECT, PLEASE CHECK PROGRESS SHEET FOR DETAILS"
.NoAging = True
.Display
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

Else
ThisWorkbook.Save
Application.Quit
End If


End Sub

Cheers

  VoG II 12:45 18 Mar 08

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 08

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 08

Ooh caught me :-(

  Picklefactory 13:06 18 Mar 08

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 08

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

  VoG II 13:18 18 Mar 08

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.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…