MsgBox

You have used the MsgBox function a number of times in this book already. Up to this point, it has only been with an OK button to acknowledge receipt of the message. However, other possibilities are available.

Table 11-1 shows the buttons and icons available to the MsgBox function. We will discuss the meaning of the values in a few moments.

Up to this point, the syntax we have used for the MsgBox has been as follows:

MsgBox "Welcome to VBA"

This will give you the result shown here.

There are two other arguments you can add to the function (actually, there is one more for a compiled help file that we will not be treating here). The second argument is the button or icon you want to use, from the list in Table 11-1, and the third is the title above the box.

If you modify the preceding code as follows:

MsgBox "Welcome to VBA", vbYesNoCancel, "VBA Message Box"

you will see these results:

You could add the Information icon, if you wanted, as follows:

MsgBox "Welcome to VBA", vbYesNoCancel + vbInformation, "VBA Message Box"

That would result in the MsgBox with the Information icon added:

Why the numeric values in Table 11-1? Those values can be substituted for the constants that indicate the name of the button. For instance, you can substitute the following syntax for the MsgBox:

MsgBox "Welcome to VBA", 3 + 64, "VBA Message Box"

This will yield the same results as shown in the previous illustration. But it also opens a lot of programming possibilities. Let’s look at the following very simple example to see what I mean.

Sub messageBoxTest()
 Dim intInput As Integer
 Dim intButtonStyle As Integer
 Dim intIconStyle As Integer
 Dim strMessage As String
 Dim strTitle As String

 strTitle = "MsgBox Example"

 intInput = InputBox("Enter either a 1 or 2")

 If intInput = 1 Then
    strMessage = "Do you want 1?"
    intButtonStyle = 4
    intIconStyle = 32
 Else
    strMessage = "I guess you wanted 2"
    intButtonStyle = 1
    intIconStyle = 64
  End If

  MsgBox strMessage, intButtonStyle + intIconStyle, strTitle
End Sub

In this example, the user is asked to enter either 1 or 2 (we have not put any code in to test for the validity of the entry). The If….Then…Else structure will set the integer values of the variables according to the values in Table 11-1. We then substitute the variables for the actual values in the MsgBox syntax.

If the user selects 1, the MsgBox shown here will come up:

But if the user selects 2, this box will be shown:

By using this technique, you can make the MsgBox reflect whatever programming situation has occurred.

We have one small problem here: all the buttons do is dismiss the box. What if you want a specific action associated with a button that has been selected?

Each of the available buttons will produce an integer result. These results are shown in this table.

Let’s look at the following code that determines which button was pressed.

Sub messageBoxTest()
 Dim intButtonPressed As Integer

 intButtonPressed = MsgBox("Welcome to VBA", vbYesNo, "Message Box Test")

 If intButtonPressed = 6 Then
    MsgBox "Yes was selected"
  Else
     MsgBox "No was selected"
   End If
End Sub

Notice in this case, when assigning the value of the MsgBox to a variable, you have to enclose the arguments in parenthesis. When it is serving as just a message box, you do not.

Based on the information in the previous table, you can see that vbYes returns an integer value of 6, and vbNo returns an integer value of 7. You could have used the following syntax to get the same results:

If intButtonPressed = vbYes Then

If you select “Yes,” you should see the response shown here:

Selecting “No” will result in this response:

Remember that all of these buttons are VB constants, which are easily identified because of the vb prefix.

The full syntax for MsgBox is

MsgBox prompt, buttons, title, helpfile, context

The only argument that is required is prompt.

InputBox

The InputBox will prompt the user for information. The value that the user enters into the box needs to be assigned to a variable. Once inside that variable, you can manipulate that value any way that you choose.

The syntax for an InputBox is as follows:

InputBox (prompt, title, default, xposition, yposition, helpfile, context)

Of these, only the prompt is required. If you do not list a default value, the input box is left empty. The x and y positions will determine the horizontal and vertical positions of the input box on the screen.