INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

How do I create a Message Box which doesn't lock the Application? by TonyJollans
Posted: 12 Jan 04 (Edited 12 Jan 04)


This FAQ is a presentation of a solution proposed by xlbo, and amended by me in response to a question in the Microsoft: Office forum (Forum68).  It is posted here rather than there because it is a VBA solution.


The MsgBox Function is familiar to all VBA programmers and provides a simple method for communicating with the User; all it does is display a message and wait for a response.

Message boxes are always modal, which means that the underlying application (e.g. Excel) waits for a response from the User and does not allow any other interaction until one is given.  By default they are application modal (they remain in front of all other windows belonging to the owning application but have no impact on other applications), but they may also be system modal (they remain in front of ALL other windows although they have no non-visual impact on applications other than the owning one).
 
So what do you do if you want to prompt for information and allow your User scope to browse the worksheet (or document) before answering, but donÆt want to go to the trouble of setting up a Userform (or you are running Office 97 and donÆt have the option of a modeless Userform)?

One option is to use the Message Box facility directly, via the Windows API, instead of via the VBA interface.  Using it is pretty much the same as using MsgBox, except that you have the opportunity to set some parameter values which are defaulted in the VBA interface, in particular the message box, although technically still modal, can be attached to any window, or none.  If you do not attach it to your window, it will not restrict your UserÆs interaction with the application.


The Windows message box routine is called MessageBoxA and it is in a library called User32.DLL.  It takes four parameters and returns a single value.

The parameters and return value are:

hWnd

The handle (a special identifier which uniquely identifies a window) of the Window which will own this message box.; if this is null, no window will own it.

Note that a ænullÆ in this context is slightly different from a normal ænullÆ in Office applications in that, although it has no value, it can have a type. For our purposes, this parameter can be specified as a literal &0 or as an uninitialised variable of type Long, or as a Long Const (value &H0).

lpText

The message to be displayed in the message box.

This is exactly the same as the first parameter (ôpromptö) to the VBA MsgBox function.

lpCaption

The text to be displayed on the title bar of the message box.

This is the same as the third parameter (ôtitleö) to the VBA MsgBox function, except for the fact that it defaults to ôErrorö rather than the application name.

wType

The sum of possibly several values which determine the way the message box will be displayed.

This is the same as the second parameter (ôbuttonsö) to the VBA MsgBox function.  For details see  the VBA MsgBox Help; the values are exactly the same (and the same enumerated constants can be used), but note that there are a couple of extra possible values for options which are (effectively) defaulted by VBA.

Note that if you want to use a Help button, you will have to use the API to process the triggered event (you canÆt specify the help file directly as you can with MsgBox).


Return Value

If the MessageBox function succeeds, it will return a number indicating which button has been pressed.  These numbers are, again, exactly the same as the ones returned by MsgBox and, again, the same enumerated constants can be used.

Note that the return value is of type long as per the function declaration but, if you use an integer (as per MsgBox) it will be cast as one.


So, now you have all the information, how do you use it?

Firstly you must declare it; that is you must tell VBA that you are going to be using an external module, and give it some details about it.  The declaration is just the same as the declaration (the first line) of a custom function you code yourself, except that it stands alone in the declarations section at the start of the module (somewhere before the first Sub or Function).  The declaration should look something like this:

    Private Declare Function MessageBox _
        Lib "User32" Alias "MessageBoxA" _
           (ByVal hWnd As Long, _
            ByVal lpText As String, _
            ByVal lpCaption As String, _
            ByVal wType As Long) _
        As Long


For those of you not familiar with this type of declaration it says you will be using function "MessageBox" which is an alias of "MessageBoxA" in library "User32.DLL", with the parameters (as detailed).  The name MessageBox can be any name of your choosing, and the names of the parameters (named using æHungarian NotationÆ) are irrelevant as you will not be referencing them again.  It is conventional to use the declaration exactly as shown (except for the line breaks, which are my own).

Once declared, you can use the MessageBox function anywhere you can use any other function so, instead of ..

    MsgBox "This is a VBA MsgBox", , "My Box"

 .. you can code ..

    MessageBox &0, "This is a native Message Box", "My Box", vbOKOnly

 .. and you will have a message box which does not lock out the application window.  This message box, however, acts just like any other window and disappears from view the minute you click outside it.  To make it stay always on top, you need to make it system modal.  This will actually make it sit on top of ALL applications your User is running but, hey, nothing is perfect ..

    MessageBox &H0, "This is a native Message Box", "My Box", vbSystemModal

To find out which button has been pressed you need to process the value returned from the function, in just the same way as you do with MsgBox:

    Dim Response As Long

    Response = MessageBox(&O0, "Do you want to dance?", "My Question", vbYesNo + vbSystemModal)

    If Response = vbYes Then
        
' Dance
    Else
        '
Sit on sidelines
    End If


You should note that, when testing and running from the VBE window, the display of the message box will not switch you to the main application window.

One final point for those wishing to experiment.  The declaration given above makes all the parameters to the MessageBox function mandatory.  Strictly speaking they are optional (and have defaults) and, as an example which also shows changed names, changing the declaration to ..

    Private Declare Function APIMsgBox _
        Lib "User32" Alias "MessageBoxA" _
           (Optional ByVal hWnd As Long, _
            Optional ByVal prompt As String, _
            Optional ByVal title As String, _
            Optional ByVal buttons As Long) _
        As Long


 .. would enable coding like this ..

    Response = APIMsgBox(prompt:="Do you want to dance?", buttons:=vbYesNo)


As well as showing how to use this particular function I have tried to include a gentle introduction to the art of using the Windows API in VBA.  I hope you find it useful; have fun with it!


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close