Display a message for a specific duration

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

Posted in Uncategorized

5 thoughts on “Display a message for a specific duration

  1. The shell popup can be encapsulated in a function that replicates the VBA MsgBox function:

    Public Function MsgPopup(Optional Prompt As String, Optional Buttons As VbMsgBoxStyle = vbOKOnly, Optional Title As String, Optional SecondsToWait As Long = 0) As VbMsgBoxResult

    ‘ 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

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

  3. 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?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *