Msgbox to automatically disappear after 2 seconds

[neodjandre]

Helllo,

I have written a macro and at the end it displays a message "The macro has finished".

I would like this message box to disappear after 2 seconds automatically so that users don't have to press the OK button all the time.

can this happen ?

thanks
andy

 

[Tom Urtis]

Stick this at the end of your macro:

Dim AckTime As Integer, InfoBox As Object
Set InfoBox = CreateObject("WScript.Shell")
AckTime = 2
Select Case InfoBox.Popup("The macro has finished.", _
AckTime, "Click OK or do nothing within 2 seconds.", 0)
Case 1, -1
Exit Sub
End Select

 

[neodjandre]

thanks Tom,

is there also a way to remove the X from the box at the top right hand corner ??

 

[neodjandre]

i have also tried 0.5 for half a second but this doesn't work !

 

[neodjandre]

bump..

 

[Tom Urtis]

If you only want to show the message box for half a second, why bother at all with it in the first place? With its text for prompt and title bar, plus its purpose, no one would be able to meaningfully comprehend that message box's intent or purpose. Just change the 2 to a 1 for seconds of wait time, or scrap the idea of a message box altogether, or use a userform to show for one second that has no title bar (to not have the "X" close button) which woule be easier to maintain with a lot less code than a message box without the "X". There are plenty of examples on this board and google for userforms without title bars.

Here's a thinking-outside-the-box idea, try a simpler approach instead of a message box or userform, to use a worksheet by putting this at the end of your code. Make sure you have screen updating on (that is, set to True) to get its effect.

Sheets.Add
Cells.Interior.ColorIndex = 6
With Range("A2")
.Value = "Macro complete"
With .Font
.Bold = True
.ColorIndex = 1
.Size = 36
End With
End With
 Application.Wait Now + TimeSerial(0, 0, 1)
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True