Talking to the User

Chapter 4 - Talking to the User

Chapter 4 - Talking to the User

4.1 Displaying Messages in the Status Bar

You can use the status bar to keep your user informed of the progress of a macro:

Displaying the Status Bar

To display a message in the status bar, set the StatusBar property of the Excel Application:

Resetting the Status Bar to Blank

To unset the status bar, just set it to False!

4.2 Displaying Messages on Screen

Syntax of the MsgBox Command

You can use the MsgBox command to display a message on screen. The arguments are:

Named Arguments / Details
Prompt / The prompt which appears in the message box
Buttons / What command buttons appear
Title / The title of the message box

Here is an example of the use of MsgBox to display a message to your user:

With a little more effort, you can customise the title of the dialog box:

Building Up Messages

You can use the symbol to concatenate (or join together) the different parts of a string:

Multiple Line Messages

Use the VbCrLf constant to specify a carriage return in a message:

Customising your Message Box

The diagram below shows the main ways in which you can customise a message box:

Button Combinations Available

The combinations of buttons available are shown here:

Symbols Available

The four available symbols are:

Named Arguments

If you have the time and energy, you can use named rather than positional arguments. The two subroutines below have identical effect, but the one on the right is much easier to read:

4.3 Finding Out Which Button Was Chosen

You can also use MsgBox as a function to control which buttons a user sees, and to determine which button a user pressed:

The Possible Buttons

You can test the result of the call to the MsgBox function against either the reserved Visual Basic constant (eg vbOK) or the value (eg 1) – they are both the same.

User chooses button / Value returned / Actual number
/ vbOK / 1
/ vbCancel / 2
/ vbAbort / 3
/ vbRetry / 4
/ vbIgnore / 5
/ vbYes / 6
/ vbNo / 7

4.4 Asking for Information

You can use the InputBox function to ask a user to type something in and then store it:

In this example, we ask the user to type in the name of a Muppet and then find this on the worksheet shown on the right.

This is the dialog box we will display:

Here is what our macro looks like: