Using the Msg Box Function

The MsgBox function that you have used so far was limited to displaying a message to the user in a simple, one-button dialog box. You closed the message box by clicking the OK button or pressing the Enter key. Create a simple message box by following the MsgBox function name with the text enclosed in quotation marks. In other words, to display the message "The procedure is complete," you should prepare the following statement:

MsgBox "The procedure is complete"

You can quickly try out the above instruction by entering it in the Immediate window. When you type this instruction and press Enter, Visual Basic displays the message box shown in Figure 4-5.

Figure 4-5:

To display a message to the user, place the text as the argument of the MsgBox function.

The MsgBox function allows you to use other arguments that make it possible to determine the number of buttons that should be available in the message box or change the title of the message box from the default, Microsoft Excel. You can also assign your own help topic. The syntax of the MsgBox is shown below.

MsgBox (prompt [, buttons] [, title], [, helpfile, context])

Notice that while the MsgBox function has five arguments, only the first one, prompt, is required. The arguments listed in square brackets are optional.

When you enter a long text string for the prompt argument, Visual Basic decides how to break the text so it fits the message box. Let's do some exercises in the Immediate window to learn various text formatting techniques.

1. Enter the following instruction in the Immediate window. Make sure to enter the entire text string on one line, and then press Enter.

MsgBox "All done. Now open ""Chap04.xls"" and place an empty disk in the diskette drive. The following procedure will copy this file to the disk."

As soon as you press Enter, Visual Basic shows the resulting dialog box.

Figure 4-6: This long message will look more appealing when you take the text formatting into your own hands.

If you get the Compile error, click OK. Then make sure that the name of the file is surrounded by double quotation marks—""Chap04.xls"".

When the text of your message is particularly long, you can break it into several lines using the VBA Chr function. The Chr function requires one argument and a number between 0 and 255, and it returns a character represented by this number. For example, Chr(13) returns a carriage return character (this is the same as pressing the Enter key), and Chr(10) returns a linefeed character (this is useful for adding spacing between the text lines).

2. Modify the instruction entered in the previous step in the following way:

MsgBox "All done." & Chr(13) & "Now open ""Chap04.xls"" and place" & Chr(13) & "an empty disk in the diskette drive." & Chr(13) & "The following procedure will copy this file to the disk."

Microsoft Excel

HI

All done.

Now open "Chap04.nls" and place

an empty disk in the diskette drive.

The following procedure will copy, this file to the jdisk.

1

{■ iJK I

You can break a long text into several lines by using the Chr(13) function.

Figure 4-7:

You can break a long text into several lines by using the Chr(13) function.

You must surround each text fragment by quotation marks. Quoted text embedded in a text string requires an additional set of quotation marks, as in ""Chap04.xls"". The Chr(13) function indicates a place where you'd like to start a new line. The string concatenation character (&) is used to add a carriage return character to a concatenated string.

When you enter exceptionally long text messages on one line, it's easy to make a mistake. As you recall, Visual Basic has a special line continuation character (an underscore _) that allows you to break a long VBA statement into several lines. Unfortunately, the line continuation character cannot be used in the Immediate window.

4. Activate the Sample6 module and enter the MyMessage subroutine shown below. Be sure to precede each line continuation character with a space.

Sub MyMessage()

MsgBox "All done." & Chr(13) _ & "Now open ""Chap04.xls"" and place" & Chr(13) _ & "an empty disk in the diskette drive." & Chr(13) _ & "The following procedure will copy this file to the disk."

End Sub

When you run the MyMessage procedure, Visual Basic displays the same message as the one shown in Figure 4-7. As you can see, the text entered on several lines is more readable, and the code is easier to maintain.

To improve the readability of your message, you may want to add more spacing between the text lines by including blank lines. To do this, use two Chr(13) or two Chr(10) functions, as shown in the following step.

5. Enter the following MyMessage2 procedure:

Sub MyMessage2()

MsgBox "All done." & Chr(10) & Chr(10) _ & "Now open ""Chap04.xls"" and place" & Chr(13) _ & "an empty disk in the diskette drive." & Chr(13)& Chr(13)_ & "The following procedure will copy this file to the disk."

End Sub

Figure 4-8 displays the message box generated by the MyMessage2 procedure.

Figure 4-8:

You can increase the readability of your message by increasing spacing between the selected text lines.

Now that you've mastered the text formatting techniques, let's take a closer look at the next argument of the MsgBox function. Although the buttons argument is optional, it's frequently used.

The buttons argument specifies how many and what types of buttons you want to appear in the message box. This argument can be a constant (see Table 4-1) or a number. If you omit this argument, the resulting message box includes only the OK button, as you've seen in the preceding examples.

Figure 4-8 displays the message box generated by the MyMessage2 procedure.

Table 4-1: Settings for the MsgBox buttons argument

Constant

Value

Description

Button settings

vbOKOnly

0

Displays only an OK button. This is the default.

vbOKCancel

1

OK and Cancel buttons

vbAbortRetryIgnore

2

Abort, Retry, and Ignore buttons

vbYesNoCancel

3

Yes, No, and Cancel buttons

vbYesNo

4

Yes and No buttons

vbRetryCancel

5

Retry and Cancel buttons

Icon settings

vbCritical

16

Displays the Critical Message icon

vb Question

32

Displays the Question Message icon

vbExclamation

48

Displays the Warning Message icon

vbInformation

64

Displays the Information Message icon

Default button settings

vbDefaultButton1

0

The first button is default

vbDefaultButton2

256

The second button is default

vbDefaultButton3

512

The third button is default

vbDefaultButton4

768

The fourth button is default

Message box modality

vbApplicationModal

0

The user must respond to the message before continuing to work in the current application

vbSystemModal

4096

All applications are suspended until the user responds to the message box

Other MsgBox display settings

vbMsgBoxHelpButton

16384

Adds Help button to the message box

vbMsgBoxSetForeground

65536

Specifies the message box window as the foreground window

vbMsgBoxRight

524288

Text is right aligned

vbMsgBoxRtlReading

1048576

Text appears as right-to-left reading on Hebrew and Arabic systems

When should you use the buttons argument? Suppose you want the user of your procedure to respond to a question with Yes or No. Your message box may then require two buttons. If a message box includes more than one button, one of them is considered a default button. When the user presses Enter, the default button is selected automatically.

Because you can display various types of messages (critical, warning, information), you can visually indicate the importance of the message by including in the buttons argument the graphical representation (icon) for the chosen message type.

In addition to the type of message, the buttons argument can include a setting to determine if the message box must be closed before a user switches to another application. It's quite possible that the user may want to switch to another program or perform another task before responding to the question posed in your message box. If the message box is application modal (vbApplication Modal), the user must close the message box before continuing to use your application. On the other hand, if you want to suspend all the applications until the user responds to the message box, you must include the vbSystemModal setting in the buttons argument.

The buttons argument settings are divided into five groups: button settings, icon settings, default button settings, message box modality, and other MsgBox display settings (see Table 4-1). Only one setting from each group can be included in the buttons argument.

To create a buttons argument, you can add up the values for each setting you want to include. For example, to display a message box with two buttons (Yes and No), the question mark icon, and the "No" button as the default button, look up the corresponding values in Table 4-1 and add them up. You should arrive at 292 (4+32+256). To quickly see the message box using the calculated message box argument, enter the following statement in the Immediate window:

MsgBox "Do you want to proceed?", 292 The resulting message box is shown below.

Figure 4-9:

You can specify the number of buttons to include in the message box by using the optional buttons argument.

When you derive the buttons argument by adding up the constant values, your procedure becomes less readable. There's no reference table where you can check the hidden meaning of 292. To improve the readability of your MsgBox function, it's better to use the constants instead of their values. For example, enter the following revised statement in the Immediate window:

MsgBox "Do you want to proceed?", vbYesNo + vbQuestion + vbDefaultButton2

The above statement produces the same result as shown in Figure 4-9.

The following example shows how to use the buttons argument inside the Visual Basic procedure.

MsgBox "Do you want to proceed?", 292 The resulting message box is shown below.

2. Activate the Sample7 module and enter the MsgYesNo subroutine shown below:

Sub MsgYesNo()

Dim question As String Dim myButtons As Integer question = "Do you want to open a new workbook?" myButtons = vbYesNo + vbQuestion + vbDefaultButton2 MsgBox question, myButtons End Sub

In the above subroutine, the question variable stores the text of your message. The settings for the buttons argument are placed in the myButtons variable. Instead of using the names of constants, you can use their values, as in the following:

myButtons =4+32+256

However, by specifying the names of the buttons argument's constants, you make your procedure easier to understand for yourself and others who may work with this procedure in the future.

The question and myButtons variables are used as arguments for the MsgBox function. When you run the procedure, you see the result displayed, as shown in Figure 4-9. Notice that the No button is now selected. It's the default button for this dialog box. If you press Enter, Excel removes the MsgBox from the screen. Nothing happens because your procedure does not have any more instructions following the MsgBox function. To change the default button, use the vbDefaultButtonl setting instead.

The third argument of the MsgBox function is title. While this is also an optional argument, it's very handy, as it allows you to create procedures that don't provide visual clues to the fact that you programmed them with Microsoft Excel. Using this argument, you can set the title bar of your message box to any text you want.

Suppose you want the MsgYesNo procedure to display in its title the text "New workbook." The following MsgYesNo2 procedure demonstrates the use of the title argument:

Sub MsgYesNo2()

Dim question As String Dim myButtons As Integer Dim myTitle As String question = "Do you want to open a new workbook?" myButtons = vbYesNo + vbQuestion + vbDefaultButton2 myTitle = "New workbook"

MsgBox question, myButtons, myTitle End Sub

The text for the title argument is stored in the variable myTitle. If you don't specify the value for the title argument, Visual Basic displays the default text "Microsoft Excel."

Notice that the arguments are listed in the order determined by the MsgBox function. If you would like to list the arguments in any order, you must precede the value of each argument with its name, as shown below:

MsgBox title:=myTitle, prompt:=question, buttons:=myButtons

The last two arguments—helpfile and context—are used by programmers who are experienced with using help files in the Windows environment. The helpfile argument indicates the name of a special help file that contains additional information you may want to display to your VBA procedure user. When you specify this argument, the Help button will be added to your message box. When you use the helpfile argument, you must also use the context argument. This argument indicates which help subject in the specified help file you want to display. Suppose HelpX.hlp is the help file you created, and 55 is the context topic you want to use. To include this information in your MsgBox function, you would use the following instruction:

MsgBox title:=mytitle, _ prompt:=question _ buttons:=mybuttons _ helpFile:= "HelpX.hlp", _ context:=55

The above is a single VBA statement, broken down into several lines with the line continuation character.

Returning Values from the MsgBox Function

When you display a simple message box dialog with one button, clicking the OK button or pressing the Enter key removes the message box from the screen. However, when the message box has more than one button, your procedure should detect which button was pressed. To do this, you must save the result of the message box in a variable. Table 4-2 shows values that the MsgBox function returns. Table 4-2: Values returned by the MsgBox function

Button Selected Constant Value

OK vbOK 1

Cancel vbCancel 2

Abort vbAbort 3

Retry vbRetry 4

Ignore vblgnore 5

Yes vbYes 6

No vbNo 7

The MsgYesNo3 procedure is a revised version of MsgYesNo2. It shows how you determine which button the user chose.

Sub MsgYesNo3()

Dim question As String Dim myButtons As Integer Dim myTitle As String Dim myChoice As Integer question = "Do you want to open a new workbook?" myButtons = vbYesNo + vbQuestion + vbDefaultButton2 myTitle = "New workbook"

myChoice = MsgBox(question, myButtons, myTitle) MsgBox myChoice End Sub

In the above procedure, you assigned the result of the MsgBox function to the variable myChoice. Notice that the arguments of the MsgBox function are now listed in parentheses:

myChoice = MsgBox(question, myButtons, myTitle)

Tip 4-11: MsgBox Function — with or without Parentheses?

Use parentheses around the MsgBox function's argument list when you want to use the result returned by the function. By listing the function's arguments without parentheses, you tell Visual Basic that you want to ignore the function's result. Most likely, you will want to use the function's result when the MsgBox contains more than one button.

When you run the MsgYesNo3 procedure, a two-button message box is displayed. By clicking on the Yes button, the statement MsgBox myChoice displays the number 6. When you click the No button, the number 7 is displayed. In Chapter 5, you will learn how to make your procedure carry out a task depending on a button's selection.

Was this article helpful?

0 0

Post a comment