The VBA Msg Box Function

VBA's MsgBox function is an easy way to display a message to the user or to get a simple response (such as OK or Cancel). I use the MsgBox function in many of this book's examples as a way to display a variable's value. The official syntax for MsgBox is as follows:

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

♦ prompt: Required. The text displayed in the message box.

♦ buttons: Optional. A numeric expression that determines which buttons and icon are displayed in the message box. See Table 12-2.

♦ title: Optional. The caption in the message box window.

♦ helpfile, context: Optional. The helpfile and help topic.

You can easily customize your message boxes because of the flexibility of the buttons argument. (Table 12-2 lists the many constants that you can use for this argument.) You can specify which buttons to display, whether an icon appears, and which button is the default.

Table 12-2 CONSTANTS USED FOR BUTTONS IN THE MSGBOX FUNCTION

Constant

Value

Description

vbOKOnly

0

Display OK button only.

vbOKCancel

1

Display OK and Cancel buttons.

vbAbortRetrylgnore

2

Display Abort, Retry, and Ignore buttons.

vbYesNoCancel

3

Display Yes, No, and Cancel buttons.

vbYesNo

4

Display Yes and No buttons.

vbRetryCancel

5

Display Retry and Cancel buttons.

vbCritical

16

Display Critical Message icon.

vbQuestion

32

Display Warning Query icon.

vbExclamation

48

Display Warning Message icon.

vblnformation

64

Display Information Message icon.

vbDefaultButtonl

0

First button is default.

vbDefaultButton2

256

Second button is default.

Constant

Value

Description

vbDefaultButton3

512

Third button is default.

vbDefaultButton4

768

Fourth button is default.

vbSystemModal

4096

All applications are suspended until the

user responds to the message box (might

not work under all conditions).

You can use the MsgBox function by itself (to simply display a message) or assign its result to a variable. When MsgBox does return a result, it represents the button clicked by the user. The following example displays a message and does not return a result:

Sub MsgBoxDemo()

MsgBox "Click OK to continue" End Sub

To get a response from a message box, you can assign the results of the MsgBox function to a variable. In the following code, I use some built-in constants (described in Table 12-3) to make it easier to work with the values returned by MsgBox:

Sub GetAnswer()

Ans = MsgBox("Continue?", vbYesNo) Select Case Ans Case vbYes ' ...[code if Ans is Yes]...

End Select End Sub

Table 12-3 CONSTANTS USED FOR MSGBOX RETURN VALUE

Constant Value Button Clicked vbOK 1 OK

vbCancel 2 Cancel

Continued

Table 12-3 CONSTANTS USED FOR MSGBOX RETURN VALUE (Continued)

Constant vbAbort vbRetry vblgnore vbYes vbNo

Value

Button Clicked

Abort

Retry

Ignore

Actually, it's not even necessary to use a variable to utilize the result of a message box. The following procedure displays a message box with Yes and No buttons. If the user doesn't click the Yes button, the procedure ends.

Sub GetAnswer2()

If MsgBox("Continue?", vbYesNo) <> vbYes Then Exit Sub ' ...[code if Yes button is not clicked]... End Sub

The following function example uses a combination of constants to display a message box with a Yes button, a No button, and a question mark icon; the second button is designated as the default button (see Figure 12-4). For simplicity, I assigned these constants to the Config variable.

Figure 12-4: The buttons argument of the MsgBox function determines which buttons appear.

Figure 12-4: The buttons argument of the MsgBox function determines which buttons appear.

Private Function ContinueProcedure() As Boolean Dim Config As Integer Dim Ans As Integer

Config = vbYesNo + vbQuestion + vbDefaultButton2 Ans = MsgBox("An error occurred. Continue?", Config) If Ans = vbYes Then ContinueProcedure = True _ Else ContinueProcedure = False End Function

The ContinueProcedure function can be called from another procedure. For example, the following statement calls the ContinueProcedure function (which displays the message box). If the function returns False (that is, the user selects No), the procedure ends. Otherwise, the next statement would be executed.

If Not ContinueProcedure() Then Exit Sub

If you would like to force a line break in the message, use the vbCrLf (or vbNewLine) constant in the text. The following example displays the message in three lines:

Sub MultiLine()

Dim Msg As String Msg = "This is the first l Msg = Msg & "Second line" Msg = Msg & "Last line" MsgBox Msg End Sub

You can also insert a tab character by using the vbTab constant. The following procedure uses a message box to display the values in a 20 x 8 range of cells (see Figure 12-5). It separates the columns by using a vbTab constant and inserts a new line by using the vbCrLF constant. The MsgBox function accepts a maximum string length of 1,023 characters, which will limit the number of cells that you can display.

Sub ShowRange()

Dim Msg As String Dim r As Integer, c As Integer Msg = "" For r = 1 To 20 For c = 1 To 8

Msg = Msg & vbCrLf Next r MsgBox Msg End Sub

Chapter 15 includes a UserForm example that emulates the MsgBox function.

ine" & vbCrLf & vbCrLf

5316

1708

1510

2318

6182

7253

1206

6905

794

2765

6922

9868

9289

4884

6065

3008

8370

1878

256

8854

1040

3491

5669

9650

7089

7938

2715

8513

1898

2779

7854

4857

noo

9566

8716

1196

9956

5575

5405

4489

1780

3269

6689

8759

1263

2166

5334

6980

9948

5494

4845

4976

5649

4

9304

4724

3036

7159

7268

3856

5390

2463

9047

8310

732

2417

7709

5476

2665

2336

41

4658

5467

916

8979

4942

108

5471

8955

9215

2569

59

6492

2497

9166

8886

6774

4214

1040

7427

3247

156

6191

57

2017

9341

4241

5851

2906

9367

7325

3749

6092

7728

1992

4

9148

2145

4508

1036

8412

1098

4005

9872

4235

6606

372

9083

5270

7289

2014

»455

3766

4412

3489

3605

7140

8567

9647

5760

3350

3960

4279

7021

4834

4152

3982

7096

6063

2137

6439

7725

411

8545

5553

8965

2712

7513

3679

8892

7902

2414

1915

4392

5234

2315

8400

3777

5053

940

Figure 12-5: This message box displays text with tabs and line breaks.

Was this article helpful?

0 0

Post a comment