Excel Macro Class: Message and Input Boxes

There are a few different options to display or request user information during macro execution and we are going to cover some of them in this lesson.

Messages in the Status Bar

Showing notifications in the status bar is a very simple but sometimes useful way to display messages during macro execution. The main advantage is that it lets you display a notification without halting execution of the code.

The status bar shows the message “Ready” by default. That changes to any relevant message while working with Excel. VBA can modify the message with the StatusBar property of the Application object.

Application.StatusBar = "Excel is importing your data. Please wait."

As this property is a member of the Application object, messages will apply to all workbooks in the Excel application. For that reason, the property should be set to null at the end of the macro, thus returning the default message (Ready). Otherwise, it remains displaying that message until any other regular Excel notification is triggered.

Message Boxes

The MsgBox function displays a message box with information and buttons, and it stops program execution until one of the buttons is clicked. We’ve seen already some examples in previous lessons and you should be familiar with the structure.

MsgBox "This will run your macro"

The message can also be set to a string variable.

Dim strMsg As String

strMsg = "This will run your macro"

MsgBox strMsg

But the MsgBox function can take more arguments and allow a more powerful user interaction. Here’s the structure for the MsgBox:

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

The messaged displayed (“This will run your macro”) is the mandatory ‘prompt” argument of the MsgBox function. But there are other useful arguments that can enhance message boxes.

A message box shows by default the “OK” button, but that can be changed by adding the ‘buttons’ argument with one of the options in the table below. There are 6 button alternatives, if we count the defaulted “OK” (value 0). You can either use the constant term or the value. Let’s see an example.

Constant	Value	Description
vbOKOnly	0	OK button only (default)
vbOKCancel	1	OK and Cancel buttons
vbAbortRetryIgnore	2	Abort, Retry, and Ignore buttons
vbYesNoCancel	3	Yes, No, and Cancel buttons
vbYesNo	4	Yes and No buttons
vbRetryCancel	5	Retry and Cancel buttons

MsgBox "Do you want to continue?", vbYesNo

MsgBox "Do you want to continue?", 4

But if we add buttons is probably because we want to get some information from the user, so we need to know which button was clicked. The MsgBox function can return a value that represents which button is clicked, and you can assign the result to a variable. The table below shows the possible return values for each button.

Constant	Value	Description
vbOK	1	OK button pressed
vbCancel	2	Cancel button pressed
vbAbort	3	Abort button pressed
vbRetry	4	Retry button pressed
vbIgnore	5	Ignore button pressed
vbYes	6	Yes button pressed
vbNo	7	No button pressed

So, in order to know if the user wants to continue or not in the example above, we need to assign a variable to the MsgBox function.

Dim userReady As VbMsgBoxResult

userReady = MsgBox("Do you want to continue", vbYesNo)

Then we can set a condition to run a particular piece of code if a certain answer is chosen.

If userReady = vbYes Then

'here's the code to continue with the task

Else

'do something else, or do nothing

End If

You can also add an icon to the message box by including the appropriate value or constant into the ‘buttons’ argument with the ‘+’ symbol. The table below shows the message box icon alternatives.

Constant	Value	Description
vbCritical	16	Critical message
vbQuestion	32	Warning query
vbExclamation	48	Warning message
vbInformation	64	Information message

Dim msgProceed As VbMsgBoxResult

msgProceed = MsgBox("Changes cannot be undone", vbOKCancel + vbCritical)

The MsgBox function accepts a third argument to change the tittle of the box (see below) and a fourth and fifth arguments to include help files in the box.

MsgBox "Do you want to continue?", vbYesNo, "Data Import"

Input Boxes

The InputBox function is used to get a single piece of information from the user. That can be a number, string, or range. InputBox accepts three arguments: prompt, title, and default.

InputBox(prompt[, title][, default]

Let’s see an example of a simple input box asking for the user’s name. The user input is assign to the uName variable.

Dim uName As String

uName = InputBox ("What’s your name?")