Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I create a Message Box which doesn't lock the Application?

VBA How To

How do I create a Message Box which doesn't lock the Application?

by  TonyJollans  Posted    (Edited  )

This FAQ is a presentation of a solution proposed by [color darkblue]xlbo[/color], 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 [color blue]MsgBox[/color] 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 [color red]always[/color] 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 [color blue]MessageBoxA[/color] and it is in a library called [color darkblue]User32.DLL[/color]. It takes four parameters and returns a single value.

The parameters and return value are:

[color blue]hWnd[/color]

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

[color blue]lpText[/color]

The message to be displayed in the message box.

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

[color blue]lpCaption[/color]

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.

[color blue]wType [/color]

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


[color darkblue] Return Value[/color]

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:

[color blue]
Code: 
    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
[/color]

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

[color blue]
Code: 
    MsgBox "This is a VBA MsgBox", , "My Box"
[/color]

.. you can code ..

[color blue]
Code: 
    MessageBox &0, "This is a native Message Box", "My Box", vbOKOnly
[/color]

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

[color blue]
Code: 
    MessageBox &H0, "This is a native Message Box", "My Box", vbSystemModal
[/color]

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:

[color blue]
Code: 
    Dim Response As Long

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

    If Response = vbYes Then
[color green]
Code: 
' Dance
[/color]
Code: 
    Else
        '
[color green]
Code: 
Sit on sidelines
[/color]
Code: 
    End If
[/color]

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

[color blue]
Code: 
    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
[/color]

.. would enable coding like this ..

[color blue]
Code: 
    Response = APIMsgBox(prompt:="Do you want to dance?", buttons:=vbYesNo)
[/color]


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!

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top