This month, Doug Steele looks at some ways to enhance the common MessageBox.
While I want to inform my users about what's going on, sometimes they forget that they need to click on the MessageBox to make things happen. Is there some way that I can have the MessageBox automatically close if they don't respond in a reasonable amount of time?
Yes there is, but with some restrictions.
While the MessageBox may be modal (so that you can't click on any other parts of your application until the MessageBox has been taken care of), some processes still do run in the background. If you can piggyback on one of those processes, you can execute some code to close your MessageBox. One of these processes is a form's Timer. It turns out that if you set the TimerInterval for a form, then, in the Timer event, you can "look" for the MessageBox and use SendMessage to close the box once you've got the MessageBox's handle. So the first task we need to tackle is getting the handle.
One approach that I've seen used for getting the Windows handle to the MessageBox is to use the GetForegroundWindow API call. However, that can be dangerous. Yes, the MessageBox will be the foremost window when Access is active, but what happens if the user switches to another application after the MessageBox has been displayed? In that scenario, when the Timer event fires and your code runs, GetForegroupWindow will return the handle to the active application. I don't think it's a good idea to close that application, rather than the MessageBox!
So what's the alternative? Just make sure you supply a Title to the MessageBox. You can then search for that title using the FindWindow API call. Of course, you'll have to make sure that the title isn't the same as the title for some other window (your application window, for instance).
I'll begin by declaring the APIs required, the WM_CLOSE constant required for the call to the SendMessage sub, and a variable to hold the Title used for the MessageBox:
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal Hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any _
) As Long
Private Const WM_CLOSE As Long = &H10
Private mstrMsgTitle As String
I've declared everything as Private; that's because I chose to place these declarations in the module associated with the form itself. If you're planning on using this capability from more than one form, you'll probably be better off making these declarations Public and placing them in a stand-alone module, so that they can be used from any form.
To invoke the MessageBox, I need code like the following, which sets the variable mstrMsgTitle to the caption to be used and the form's TimerInterval property to the number of milliseconds to wait before firing the Timer event (in this case, 10,000 milliseconds = 10 seconds):
Private Sub cmdDisplay_Click()
Me.TimerInterval = 10000
mstrMsgTitle = "Smart Access MsgBox"
MsgBox "I should disappear in 10 seconds.", _
vbOkOnly + vbInformation, _
Finally, I need to put code in the form's Timer event to find the MessageBox and shut it down. For reasons I'm not really sure of, Access seems to add a blank to the end of the caption in the MessageBox, so I must search for what's stored in mstrMsgTitle plus a terminating blank. Window handles are always non-zero, so I check that I have a valid handle before trying to shut the MessageBox down. Once the MessageBox is shut down, I set the TimerInterval back to 0 so that the event won't run again:
Private Sub Form_Timer()
Dim hMsgBox As Long
hMsgBox = FindWindow( _
vbNullString, mstrMsgTitle & " ")
If hMsgBox <> 0 Then
Call SendMessage( _
hMsgBox, WM_CLOSE, 0, ByVal 0&)
Me.TimerInterval = 0
At the start of this answer, I stated that there were some restrictions to my solution. The requirement for a unique MessageBox Title caption is one, but there's another more serious restriction. The WM_CLOSE method of the SendMessage function essentially simulates clicking on the Close button (the X in the upper right-hand corner of the window). As you know, there are six different combinations of buttons available to be put on the MessageBox (vbOkOnly, vbOkCancel, vbAbortRetryIgnore, vbYesNoCancel, vbYesNo, and vbRetryCancel). It turns out that the Close button is only active on MessageBoxes that have a Cancel button on them and ones that have only the OK button. In other words, you cannot use this method with a MessageBox that uses either vbAbortRetryIgnore or vbYesNo. Regardless of which button is highlighted as the default button, closing the MessageBox this way will return vbCancel except when the MessageBox has only the OK button, in which case the MessageBox returns vbOK.
Access 97 had a nice feature that let you produce a formatted MessageBox that made it easy to highlight part of the message. Is there any way to do that in newer versions of Access?
For those of you who don't know what the question is referring to, in Access 97 you could create formatted MessageBoxes by using the @ character in your text to divide the text into three sections. The first section of text in the message would be displayed as a bold heading, the second section would be displayed as plain text beneath that heading, and the third section would be displayed as plain text beneath the second section, with a blank line between them. For instance, you could use the following syntax to produce the MessageBox shown in Figure 1:
"Error!@That button doesn't work.@Try again", _
vbOKOnly + vbCritical
In addition, if the Office Assistant was turned on, Access 97 would use the Office Assistant to display the message rather than a MessageBox.
However, starting with Access 2000, the language that you're coding with is no longer Access' version of Visual Basic but a version of VBA common to all Office applications. As a result, the MsgBox function that produces the MessageBox doesn't call back into Access anymore. This means you lose both formatted MessageBoxes and the ability to have the Office Assistant "front" for your messages. But there's a workaround (courtesy of Michael Kaplan ). You can use the Eval function to run the MsgBox function. Doing this means that your call will go through the Expression Service that interfaces with Access and Jet, and will call into the Access version of MsgBox instead of the VBA version:
Function FormattedMsgBox( _
Prompt As String, _
Optional Buttons As VbMsgBoxStyle = vbOKOnly, _
Optional Title As String = vbNullString, _
Optional HelpFile As Variant, _
Optional Context As Variant) _
Dim booNoHelp as Boolean
booNoHelp = IsMissing(HelpFile) Or _
If booNoHelp Then
FormattedMsgBox = Eval( _
"MsgBox(""" & Prompt & _
""", " & Buttons & ", """ & Title & """)")
FormattedMsgBox = Eval( _
"MsgBox(""" & Prompt & _
""", " & Buttons & ", """ & Title & """, _
""" & _
HelpFile & """, " & Context & ")" _
Can I change the captions on the buttons of a MessageBox? For instance, I want the buttons to say "Run Again" and "Quit" rather than "Yes" and "No."
Again, it can be done, but it's a little tricky. What you need to do is use hooks and callback functions. There are a few different ways of doing this, but the method that I find works most consistently is to use two separate callback functions: one to change the properties of the MessageBox (executed a fraction of a second after the MessageBox is invoked), and (optionally) a second function to kill the MessageBox.
Since this involves rather detailed API coding, I don't intend to show the code here. (The downloadable database, which comes in both Access 97 and Access 2000 formats, is fairly extensively commented, so I believe that it can stand alone.) I'll just touch on some of the highlights here.
The essence of the code is that I generate the MessageBox in the usual manner and then use API calls to locate the components in the MessageBox that need to be changed. Specifically, I create a timer (using the SetTimer API call this time) that causes a second function to be invoked almost immediately after the MessageBox is displayed (we're talking 10 to 20 milliseconds later).
One of the reasons I created separate downloadable databases in Access 97 and Access 2000 formats is that the AddressOf operator (a unary operator that causes the address of a procedure to be passed to an API procedure), which is required for callback functions, wasn't introduced into Access until Access 2000. However, Michael Kaplan and Ken Getz (both of whom will be familiar to long-time readers of Smart Access) found an undocumented equivalent, which I use in the Access 97 version. The SetTimer function expects to be passed the address of a procedure, so without Michael and Ken's contribution, this solution would be a non-starter in Access 97.
To create the timer that calls a function named ChangeMsgBox after 20 milliseconds in Access 97, I use this code (where AddrOf is the function that Ken and Michael discovered):
Call SetTimer(Application.hWndAccessApp, _
0&, 20&, AddrOf("ChangeMsgBox"))
In Access 2000 and newer, you should use this code:
Call SetTimer(Application.hWndAccessApp, _
0&, 20&, AddressOf ChangeMsgBox)
In this code, Application.hWndAccessApp returns a handle to Access itself. I believe the code would work just as well if you used 0& instead.
The first thing the callback function ChangeMsgBox does is locate the MessageBox that was just created. While the FindWindow API function should do that (especially since I know both the Class ID and caption of the MessageBox), in practice I found that the results were inconsistent, so I ended up looping through all of the open windows one by one until I found the MessageBox I was looking for. To do this, I use the GetDesktopWindow API function to return the handle of the desktop, and then I determine the handle of each "child" of the desktop (using the GetWindow API function with parameters of GW_CHILD and GW_HWNDNEXT).
For each child window I find, the GetWindowText API function will return the window's caption, while the GetClassName API function will return its class name. Each window has a class name determined by what created it. For instance, the class name for a window opened by Access is "OMain," for one opened by Excel is "XLMain," and for one opened by Word is "OpusApp." MessageBoxes always have a class name of "#32770."
Once I've located the MessageBox, I need to locate the buttons that I want to change (which means, of course, that I need to first ensure that those components exist on the MessageBox...). For example, in order to change the captions on the buttons, I need to know which buttons exist on the form so that I can address them individually. As you know, when you click on a given button, the MessageBox function returns an ID associated with the button you pushed. If the MessageBox has an OK button and you click it, the function returns vbOK (which is equal to 1). Well, it turns out that Windows identifies the button on the MessageBox by the same number: The OK button will be control 1, while the Cancel button will be vbCancel or control 2. So I know the handle of the MessageBox (call it hMsgBox), I know the ID associated with the button (call it lngButton), and I know the caption I want to put on that button (strText). Armed with that information, I can use the SendDlgItemMessage API call to make the change:
Call SendDlgItemMessage(hMsgBox, lngButton, _
WM_SETTEXT, 0&, strText)
In this case, WM_SETTEXT is a system constant equal to 12, and the 0 is a placeholder because that parameter isn't required in this case.
Similarly, if I want to change what icon appears in the MessageBox, then there must already be an icon on the MessageBox for me to change. Unlike the buttons, though, the ID for the icon varies from version to version of Access and of Windows. That means that I actually have to search for the icon rather than accessing it directly by its ID.
Again, I can use the GetWindow API function to determine the handle for each of the controls on the MessageBox. For each of the controls, I determine the class name (using the GetClassName API function) and the style of the window (using the GetWindowLong API function). The icon control will have a class name of "STATIC" and a style of SS_ICON (which is equal to 3). I also need to load the icon from the file (using the LoadImage API function) and get the handle to that loaded icon. Given the full path to the icon file (strIconPath) and the size I want the icon to be (lngSize, which I'll set to 16, as in a 16x16 bit icon), I can get the handle hIcon with this code:
hIcon = LoadImage(0&, strIconPath, IMAGE_ICON, _
lngSize, lngSize, LR_LOADFROMFILE)
In this call, IMAGE_ICON is a system constant equal to 1 and LR_LOADFROMFILE is a system constant equal to 16.
As before, since I know the handle of the MessageBox (hMsgBox), the ID associated with the icon control (lngIconID), and the handle of the image I want to load in that control (hIcon), I can use the SendDlgItemMessage API call to make the change:
Call SendDlgItemMessage(hMsgBox, lngIconID, _
STM_SETIMAGE, IMAGE_ICON, hIcon)
Here STM_SETIMAGE is a system constant equal to 370, and IMAGE_ICON is a system constant equal to 1.
Setting an icon on the MessageBox's caption bar is simpler. All that's required is a call to the SendMessage API function, giving it the handle of the MessageBox (hMsgBox) and the handle of the image I want to load on the caption bar (hIcon):
Call SendMessage(hMsgBox, WM_SETICON, _
0&, ByVal hIcon)
However, for this to work, I need to ensure that there's room for the icon. That means that when I create the MessageBox in the first place, I need to add a few spaces to the caption as a placeholder for the icon. Once I set the icon on the caption bar, I then reset the window's caption to its original, unpadded caption, using the SetWindowText API function:
Call SetWindowText(hMsgBox,, Trim$(strTitle))
Okay, that's essentially all it takes to make all of the cosmetic changes to the MessageBox. The final thing I do in the ChangeMsgBox function is determine whether or not the MessageBox is supposed to be shut down after a given period of time. If it is, I again use the SetTimer API call to create a timer that will invoke another function at the end of the period of time.
In Access 97, I use this code:
Call SetTimer(Application.hWndAccessApp, 0&, _
In Access 2000 and newer, the equivalent code is:
Call SetTimer(Application.hWndAccessApp, 0&, _
lngDuration, AddressOf KillMsgBox)
In these two examples, lngDuration is how long the MessageBox is supposed to be displayed in milliseconds.
The only thing worth mentioning in the KillMsgBox function is that it uses the value passed as the Buttons parameter in the MessageBox call to determine which button is supposed to be clicked. It does this by determining which button selection was used by Anding the various options vbRetryCancel, vbYesNo, vbYesNoCancel, and so on with what was passed; it then determines which button is the default by Anding the options vbDefaultButton2 or vbDefaultButton3 with what was passed. Note that while most of the possible parameters are binary values, the ones for the button selection (vbRetryCancel, vbYesNo, and so forth) aren't. That complicates things a little bit because the sequence in which you check for the buttons becomes important. What I'm trying to say is that if what was passed (lngPassed) is comprised of several values (say either Value1 Or Value2 Or Value2 or Value1 + Value2 + Value3), you usually check whether a value is part of what was passed using this code:
If (lngPassed Or Value1) = Value1 Then
However, vbOKCancel is 1, vbAbortRetryIgnore is 2, and vbYesNoCancel is 3. If one of the values contributing to lngPassed is vbYesNoCancel, then all of the following will be true:
(lngPassed And vbOKCancel) = vbOKCancel
(lngPassed And vbAbortRetryIgnore) = _
(lngPassed And vbYesNoCancel) = vbYesNoCancel
To get around this problem, you need to check the values in descending order.
All of the code and the workarounds are in the downloadable databases, which also include all of the code in this article.
By the way, I feel that this is one of those "use at your own risk" hacks. It works fine for me, but I'd test very thoroughly before using this in a production application!
Your download file is called 505STEELE.ZIP in the file SA2005-05down.zip
This can be purchased with all the other downloads on this page
Other Pages That You Might Like