Microsoft Office Tutorials and References
In Depth Information
The VBA MsgBox Function
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
You can call the ContinueProcedure function 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
The width of the message box depends on your video resolution. If you’d 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. Figure 12-6 shows how it looks.
Figure 12-6: Splitting a message into multiple lines.
Dim Msg As String
Msg = “This is the first line.” & vbCrLf & vbCrLf
Msg = Msg & “This is the second line.” & vbCrLf
Msg = Msg & “And this is the last line.”
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 13 x 3 range of cells in A1:C13 (see Figure 12-7). 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. Also, note that the tab stops are fixed, so if a cell
contains more than 11 characters, the columns won’t be aligned.