There are a number of instances where one may want to show a message for a specific duration. In other instances it might be for a specific duration or until the user acknowledges the message, whichever comes first. In the first category would be something like a splash screen or a very brief acknowledgment of some activity. In the latter category might fall a custom splash screen or a check to see if the workbook is still in use.
For a post in a page by itself (i.e., not in a scrollable iframe control) see http://www.tushar-mehta.com/publish_train/xl_vba_cases/1023_display_message_for_a_specific_duration.shtml
The shell popup can be encapsulated in a function that replicates the VBA MsgBox function:
‘ Replicates the VBA MsgBox() function, with an added parameter
‘ to automatically dismiss the message box after n seconds
‘ If dismissed automatically, this will return -1: NOT ‘cancel’ or the default button choice.
‘ Nigel Heffernan, 2006. This code is in the public domain.
‘ Uses late-binding: bad for performance and stability, useful for code portability
‘ The correct declaration is: Dim objWshell As IWshRuntimeLibrary.WshShell
Dim objWshell As Object
Set objWshell = CreateObject(“WScript.Shell”)
MsgPopup = objWshell.Popup(Prompt, SecondsToWait, Title, Buttons)
Set objWshell = Nothing
End Function
Wiil this work in 2007? or is this only in 2010?
These are great tips…
Unfortunately, whilst the Shell Popup method may work in some systems it doesn’t work in all systems. The Msgbox may timeout at some completely different time to that set, or not at all until dismissed manually.
I’m not aware of any obvious reason as to why it works for some yet not for others, assuming WshShell is enabled of course, but it doesn’t seem to be OS or Excel version related.
[…] PM | In General | Leave a Comment Tags: VBA, popup, timeout, notification Tushar Mehta writes on how to show a short-lived pop-up message, so that the user may cancel it, or just wait for it to go […]
If a message is important enough to show on the screen while the user might be paying attention to other things, it is important enough to leave on the screen until you know the user has seen it. Unless you have a way to track the user’s eyes, you don’t know whether the user has read the message. And there aren’t many things more annoying than being distracted from what you’re doing by a something unexpected changing on the screen and then seeing a message disappear before you have a chance to read it. It’s like someone tapping you on the shoulder from behind and saying “Hey!”, then walking away without saying anything.
Timed message are based on the assumption that the user is watching the screen
for the entire arbitrary time limit the programmer has set and doesn’t care about the message if he wasn’t. Why make poor assumptions if you can ask? Why not let the user decide whether he wants messages to stay visible until acknowledged?