Excel 2010 Bible

Part VI: Programming Excel with VBA

842

The Sub procedure that follows is another example of using the MsgBox function:

Sub GetAnswer2()

Msg = “Do you want to process the monthly report?”

Msg = Msg & vbNewLine & vbNewLine

Msg = Msg & “Processing the monthly report will take approximately“

Msg = Msg & “15 minutes. It will generate a 30-page report for all“

Msg = Msg & “sales offices for the current month.”

Title = “XYZ Marketing Company”

Config = vbYesNo + vbQuestion

Ans = MsgBox(Msg, Config, Title)

If Ans = vbYes Then RunReport

If Ans = vbNo Then Exit Sub

End Sub

This example demonstrates an efficient way to specify a longer message in a message box. A vari-

able (Msg) and the concatenation operator (&) are used to build the message in a series of state-

ments. vbNewLine is a constant that represents a break character. (Using two line breaks inserts a

blank line.) The title argument is also used to display a different title in the message box. The

Config variable stores the constants that generate Yes and No buttons and a question mark icon.

Figure 41.5 shows how this message box appears when the procedure is executed.

FIGURE 41.5

A message box with a longer message and a title.

Creating UserForms: An Overview

The InputBox and MsgBox functions are adequate for many situations, but if you need to obtain

more information, you need to create a UserForm.

The following is a list of the general steps that you typically take to create a UserForm:

1. Determine exactly how the dialog box is going to be used and where it is to fit into
   your VBA macro.