Found this code below on-line, it is designed to close a file after it has
been open for a period time. The problem is that the MsgBox sits there
waiting for a reply. I want the MsgBoxes to display so the user will know
what is happening, but I would like them to display for only about 5 to 10
seconds and then disappear and the code to continue.

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 6 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You
have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit

It is probably easier and more flexible to use a UserForm with a label for
the messagel Then you can show it and unload it with regular code.

UserFrom1.Show
Wait(Now + TimeValue("00:00:05")
Unload UserForm1


"ordnance1" wrote in message
...
Found this code below on-line, it is designed to close a file after it has
been open for a period time. The problem is that the MsgBox sits there
waiting for a reply. I want the MsgBoxes to display so the user will know
what is happening, but I would like them to display for only about 5 to 10
seconds and then disappear and the code to continue.

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 6 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You
have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit

hi, !

Found this code below on-line, it is designed to close a file after it has been open for a period time.
The problem is that the MsgBox sits there waiting for a reply.
I want the MsgBoxes to display so the user will know what is happening
but I would like them to display for only about 5 to 10 seconds and then disappear and the code to continue...

change this part:
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have 5 minutes to save before Excel closes."

for this one:
CreateObject("wscript.shell").Popup _
"This file has been open for " & TotalTime / 60 & " minutes." & vbCr & _
"You have 5 minutes to save before Excel closes." & vbCr & _
"This message self-closes in 5 seconds...", 5, "Temp message"

hth,
hector.

__ code __
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimeInMinutes = 6 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit

Thank you sir I will give that a try when tommorrow when I get back to work.

"Héctor Miguel" wrote:

hi, !

Found this code below on-line, it is designed to close a file after it has been open for a period time.
The problem is that the MsgBox sits there waiting for a reply.
I want the MsgBoxes to display so the user will know what is happening
but I would like them to display for only about 5 to 10 seconds and then disappear and the code to continue...

change this part:
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have 5 minutes to save before Excel closes."

for this one:
CreateObject("wscript.shell").Popup _
"This file has been open for " & TotalTime / 60 & " minutes." & vbCr & _
"You have 5 minutes to save before Excel closes." & vbCr & _
"This message self-closes in 5 seconds...", 5, "Temp message"

hth,
hector.

__ code __
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimeInMinutes = 6 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit


.

Thank you sir

"JLGWhiz" wrote:

It is probably easier and more flexible to use a UserForm with a label for
the messagel Then you can show it and unload it with regular code.

UserFrom1.Show
Wait(Now + TimeValue("00:00:05")
Unload UserForm1


"ordnance1" wrote in message
...
Found this code below on-line, it is designed to close a file after it has
been open for a period time. The problem is that the MsgBox sits there
waiting for a reply. I want the MsgBoxes to display so the user will know
what is happening, but I would like them to display for only about 5 to 10
seconds and then disappear and the code to continue.

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 6 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You
have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit


.

Sorry, I took a couple of days off and have just gotten back to this.

This is the test code I have tried. It calls the UserForm but it never closes.


Sub test()

UserForm1.Show
Application.Wait (Now + TimeValue("00:00:05"))
Unload UserForm1

End Sub


"JLGWhiz" wrote:

It is probably easier and more flexible to use a UserForm with a label for
the messagel Then you can show it and unload it with regular code.

UserFrom1.Show
Wait(Now + TimeValue("00:00:05")
Unload UserForm1


"ordnance1" wrote in message
...
Found this code below on-line, it is designed to close a file after it has
been open for a period time. The problem is that the MsgBox sits there
waiting for a reply. I want the MsgBoxes to display so the user will know
what is happening, but I would like them to display for only about 5 to 10
seconds and then disappear and the code to continue.

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 6 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You
have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit


.

Set UserForm1 showmodal property to false.


Gord Dibben MS Excel MVP

On Mon, 26 Apr 2010 17:44:05 -0700, ordnance1
wrote:

Sorry, I took a couple of days off and have just gotten back to this.

This is the test code I have tried. It calls the UserForm but it never closes.


Sub test()

UserForm1.Show
Application.Wait (Now + TimeValue("00:00:05"))
Unload UserForm1

End Sub


"JLGWhiz" wrote:

It is probably easier and more flexible to use a UserForm with a label for
the messagel Then you can show it and unload it with regular code.

UserFrom1.Show
Wait(Now + TimeValue("00:00:05")
Unload UserForm1


"ordnance1" wrote in message
...
Found this code below on-line, it is designed to close a file after it has
been open for a period time. The problem is that the MsgBox sits there
waiting for a reply. I want the MsgBoxes to display so the user will know
what is happening, but I would like them to display for only about 5 to 10
seconds and then disappear and the code to continue.

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 6 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You
have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit


.