VBA: User Form Pop Up and then go away after a certain time

[bamaisgreat]

Thanks for looking at my post.. I would like to have a user form pop at 2 certain times with a simple message in it and a OK button. If someone has not clicked on the button by a specific time the user form will close so it will allow other macros to run...... I used the code below for msgbox but Could not find a solution on how to close the msgbox at a specific time...

Sub ShowMsg()
If TimeValue(Now()) > "1:58:00 PM" Then
End If
If TimeValue(Now()) > "1:59:00 PM" Then
Exit Sub
End If
Application.OnTime Now + TimeValue("00:00:01"), "TimedMsgBox"
End Sub
Sub TimedMsgBox()
If TimeValue(Now()) = "1:58:00 PM" Then
MsgBox "SEND FORM AT END OF DAY SHIFT "
End If
If TimeValue(Now()) = "1:59:00 PM" Then
MsgBox "SEND FORM AT END OF DAY SHIFT "
End If
ShowMsg
End Sub

 

[hippiehacker]

several ways to go

1. Set a reference to "Windows Script Host Object Model" and then use
code like

Dim SH As IWshRuntimeLibrary.WshShell
Set SH = New IWshRuntimeLibrary.WshShell
SH.Popup "Hello World", 5, "Title", vbYesNo

This will show a message box for 5 seconds, If the Popup times out with no user input, the
result is -1!!!

2. Use a custom form, and set the OnTime method when you initialise it to call
a proc that times out.

See OnTime in Help for details of that method.

3. try this

Sub TimedMessage()
Const Title As String = "Self closing message box"
Const Delay As Byte = 10 ' show time in seconds
Const wButtons As Integer = 16 ' buttons and Icons
Dim wsh As Object, msg As String
Set wsh = CreateObject("WScript.Shell")
msg = Space(10) & "SEND FORM AT END OF DAY SHIFT"
wsh.Popup msg, Delay, Title, wButtons
Set wsh = Nothing
End Sub

for no buttons use

set Const wButtons As Integer = 7 + 16