Emulating the MsgBox Function | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

VBA's MsgBox function is a bit unusual because, unlike most functions, it displays a dialog box. But, similar to other functions, it also returns a value: an integer that represents which button the user clicked.

This section describes a custom function that I created that emulates VBA's MsgBox function. On first thought, creating such a function might seem rather easy. Think again! The MsgBox function is extraordinarily versatile because of the arguments that it accepts. Consequently, creating a function to emulate MsgBox is no small feat.

Note

The point of this exercise is not to create an alternative messaging function. Rather, it's to demonstrate how to develop a relatively complex function that also incorporates a UserForm. However, some people might like the idea of being able to customize their messages. If so, you'll find that this function is very easy to customize. For example, you can change the font, colors, button text, and so on.

I named my pseudo- MsgBox function MyMsgBox . The emulation is close, but not perfect. The MyMsgBox function has the following limitations:

It does not support the Helpfile argument (which adds a Help button that, when clicked, opens a Help file).
It does not support the Context argument (which specifies the context ID for the Help file).
It does not support the system modal option, which puts everything in Windows on hold until you respond to the dialog box.
It does not play a sound when it is called.

The syntax for MyMsgBox is

 MyMsgBox(  prompt  [,  buttons  ] [,  title  ])

This syntax is exactly the same as the MsgBox syntax except that it doesn't use the last two optional arguments ( Helpfile and Context ). MyMsgBox also uses the same predefined constants as MsgBox : vbOKOnly , vbQuestion , vbDefaultButton1 , and so on.

Note	If you're not familiar with the VBA `MsgBox` function, consult the Help system to become familiar with its arguments.

MsgBox emulation: MyMsgBox code

The MyMsgBox function uses a UserForm named MyMsgBoxForm . The function itself, which follows , is very short. The bulk of the work is done in the UserForm_Initialize procedure.

CD-ROM

The complete code for the MyMsgBox function is too lengthy to list here, but it's available in a workbook named msgbox emulation.xlsm , available on the companion CD-ROM. The workbook is set up so you can easily try various options.

 Public Prompt1 As String Public Buttons1 As Integer Public Title1 As String Public UserClick As Integer Function MyMsgBox(ByVal Prompt As String, _   Optional ByVal Buttons As Integer, _   Optional ByVal Title As String) As Integer     Prompt1 = Prompt     Buttons1 = Buttons     Title1 = Title     MyMsgBoxForm.Show     MyMsgBox = UserClick End Function

Figure 15-11 shows MyMsgBox in use. It looks very similar to the VBA message box, but I used a different font for the message text (Calibri 12-point bold).

Figure 15-11: The result of the MsgBox emulation function.

Here's the code that I used to execute the function:

 Prompt = "You are about to wipe out your entire hard drive." Prompt = Prompt & vbCrLf & vbCrLf & "OK to continue?" Buttons = vbQuestion + vbYesNo Title = "We have a problem" Ans = MyMsgBox(Prompt, Buttons, Title)

Note	This example, of course, does not really wipe out your entire hard drive.

How the MyMsgBox function works

Notice the use of four Public variables . The first three ( Prompt1 , Buttons1 , and Title1 ) represent the arguments that are passed to the function. The other variable ( UserClick ) represents the values returned by the function. The UserForm_Initialize procedure needs a way to get this information and send it back to the function, and using Public variables is the only way to accomplish that.

The UserForm (shown in Figure 15-12) contains four Image controls (one for each of the four possible icons), three CommandButton controls, and a TextBox control.

Figure 15-12: The UserForm for the MyMsgBox function.

The code in the UserForm_Initialize procedure examines the arguments and does the following:

Determines which, if any, image to display (and hides the others)
Determines which button(s) to display (and hides the others)
Determines which button is the default button
Centers the buttons in the dialog box
Determines the captions for the CommandButtons
Determines the position of the text within the dialog box
Determines how wide to make the dialog box (by using an API function call to get the video resolution)
Determines how tall to make the dialog box
Displays the UserForm

Three additional event handler procedures are included (one for each CommandButton). These routines determine which button was clicked and return a value for the function by setting a value for the UserClick variable.

Interpreting the second argument ( buttons ) is a bit challenging. This argument can consist of a number of constants added together. For example, the second argument can be something like this:

 VbYesNoCancel + VbQuestion + VbDefaultButton3

This argument creates a three-button MsgBox (with Yes, No, and Cancel buttons), displays the Question icon, and makes the third button the default button. The actual argument is 547 (3 + 32 + 512).

The challenge was pulling three pieces of information from a single number. The solution involves converting the argument to a binary number and then examining specific bits. For example, 547 in binary is 1000100011. Binary digits 4 through 6 determine the image displayed; digits 8 through 10 determine which buttons to display; and digits 1 and 2 determine which button is the default button.

Using the MyMsgBox function in the MsgBox emulation

To use this function in your own project, export the MyMsgBoxMod module and the MyMsgBoxForm UserForm. Then import these two files into your project. You can then use the MyMsgBox function in your code just as you'd use the MsgBox function.