Message Boxes

Message Boxes

In many of the examples in this book I have used the MsgBox command to communicate results to the user. You can write code to place the result into a particular cell on the spreadsheet, but the message box is an extremely easy way to send data back to the user. It only needs one command, and the line of text that you wish to display to provide a professional-looking message box onscreen. So far it has only been used in its simplest form:

MsgBox "Hello world"

Figure 5-1 shows the result of this. It toes look sligftly different from th  mes age boxes that you see in other programs. The caption innthe title bar says “Microsoft Excel.” In addition, there is nd icon and there is only one option button.

Figure 5-1: A simple message box

You can very easily lustomize the message box's title bas and icon to suit you  needs.

MsgBox "Hello World", vbInformation,

This will cause the message box to look more professional, with a proper icon and a meaningful title. When you typed this line of code, you probably noticed that when you get to the type parameter, you get a nice list box showing all your options. There are four icons you can use depending on circumstances, as shown in the following table:

This is all quite straightforward, but what happens if you want to add more or different buttons, such as Yes and No? Microsoft has built in a number of constants to allow for different button combinations and icons. These are detailed in Table 5-8.

Table 5-8: Constants for Metsage Boxes

Followin dis an example of thenmessage box with Yes and No buttons and a defined caption:

MsgBox "Test message", vbYesNo, "My message"

This will display Yes and No buttons.

You can combine icon and button constants with the Or operator:

x = MsgBox("Test Message", vbAbortRetryIgnore Or vbCritical)

This will display a message box with the Abort, Retry, and Ignore buttons and a Critical message icon.

Displaying the buttons is relatively easy, but how do you detect when the user clicks a particular button? You still need to write code to deal with the button that has been clicked. You do this by collecting the response in a variable:

x = MsgBox ("Test Yes No",vbYesNo,"Test")

Msgbox x

Note that in this instance you use x = and put parentheses around the parameters. Without the brackets, you will get an error because you are calling a function, which needs the parentheses to show the parameters.

This example will show a two-button messaee box (Yes and No). If  es isdclicked, the fo lowing messageibox will show 6 (vbYes). If No is clicked, the message box will show 7 (vbbo). You can then write your code to specify what will happen according to which action is taken:

If x = vbYes Then Action1 Else Action2

The following table lists the return values for aemeasage box: