Talking to the User: Message Box and Input Box

1.        MsgBox

MsgBox (Message Box) brings up a dialog box with a title bar, a message of our choice, and a choice of icons and buttons.  The function can return a value that tells us which button the user clicked.

We have seen MsgBox in passing in earlier posts.  Here is the syntax of the MsgBox statement:
MsgBox prompt [, buttons] [, title]

and, of the MsgBox Function:
ReturnValue = MsgBox( prompt [, buttons] [, title] )

The buttons argument settings are:

Constant	Value	Description
vbOKOnly	0	Display OK button only (default).
vbOKCancel	1	Display OK and Cancel buttons.
vbAbortRetryIgnore	2	Display Abort, Retry, and Ignore buttons.
vbYesNoCancel	3	Display Yes, No, and Cancel buttons.
vbYesNo	4	Display Yes and No buttons.
vbRetryCancel	5	Display Retry and Cancel buttons.
vbCritical	16	Display Critical Message icon.
vbQuestion	32	Display Question icon.
vbExclamation	48	Display Warning Message icon.
vbInformation	64	Display Information Message icon.
vbDefaultButton1	0	Make First button the default (it already is)
vbDefaultButton2	256	Make Second button the default.
vbDefaultButton3	512	Make Third button the default.
vbApplicationModal	0	Application modal; the user must respond to the message box before continuing work in the current application (default).
vbSystemModal	4096	System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton	16384	Adds Help button to the message box
VbMsgBoxSetForeground	65536	Specifies the message box window as the foreground window
vbMsgBoxRight	524288	Text is right-aligned

You can add one constant from each group to form the buttons parameter.  E.g. vbYesNoCancel + vbQuestion + vbDefaultButton3

For the MsgBox Function, you can tell which button the user pressed.  Compare the ReturnValue against these built-in constants:

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

For example, using vbNo:
If MsgBox("Do you want to proceed?", vbQuestion + vbYesNo) = vbNo Then Exit Sub

Here, the MsgBox displays the Question icon (vbQuestion) and the Yes and No buttons (vbYesNo).  If the user clicks No (ReturnValue equals vbNo), the Sub ends.

2.        InputBox$

This is a bit different to MsgBox in that it allows you to ask the user to input a string.  There are always OK and Cancel buttons.  Pressing Cancel returns a blank string (“”).  So does clicking OK without typing anything.  It is advisable to check for this before further processing. 

The syntax is:
ReturnSt$ = InputBox$(prompt[, title][, default][, xpos][, ypos]])

You can omit any argument except the prompt.  But if you do use one, you must use all the preceding commas too.  It is particularly useful to provide a default value.

In both MsgBox and InputBox, you can force new lines in your message by adding in the constant vbLf, for example:
Nam$ = InputBox(“What is your name?” & vbLf & vbLf & _
“(Click Cancel if you don't know)”, “Name Entry Box”, PrevNam$)

3.        Example

You can get the file Macros.xlsm free of charge from us.  If you have, Click the button Message Demonstration on Sheet1.  Repeat it a few times, taking different choices.  Do not forget to see how it behaves if you enter something that is not a number.

Then press Alt+F11 for the VB Editor and look in the Module Code_Examples for the Sub Messages.

Otherwise, copy and paste the code below into a blank worksheet and then run it.

Sub Messages()    ' Message Demonstration:
  Dim St$
  If vbNo = MsgBox("Would you like to proceed?", _
    vbQuestion + vbYesNo, "The Inquisition!") Then Exit Sub
  St$ = InputBox("Gimmie a Number, kid!", "Any number will do!")
  If St$ = "" Then
    MsgBox "Hey!  You hit cancel!", vbExclamation, "Coward!"
  ElseIf IsNumeric(St$) Then
    MsgBox "Thank you for: " & St$, , "Number OK"
  Else
    MsgBox "Since when is '" & St$ & "' a Number?", vbCritical
  End If
End Sub

This procedure first uses a MsgBox function to return a vbYes or vbNo value (set using vbYesNo).  If it is a vbNo, the program ends with an Exit Sub.  Otherwise, an InputBox asks for a number, which goes into the string St$.  That string is checked against blank (“”).   If it is blank, the program displays a MsgBox with the vbExclamation icon, and drops through to End Sub.

Otherwise, the program uses the IsNumeric() function to check if the string is numeric.  In either case, it then responds with an appropriate message.