MODULES▼
LESSONS▼
This module shows how you can display message boxes on screen, and use input boxes to get information from users of your programs.
Choose what you want to learn from the list of lessons above.
This page provides a brief summary of what you will learn (or have learned) in this module. You can click here to download the example code shown below.
Displaying Messages
You can use the MsgBox function to display a message to the user - for example:
MsgBox "Here's a message!"
You can build a message by concatenating different values - for example:
MsgBox _
"The active cell is " & ActiveCell.Address & _
vbNewLine & _
"Its value is " & ActiveCell.Value
You can customise a message box with a title and icon - for example:
MsgBox _
Prompt:="Here's a message!", _
Buttons:=vbInformation, _
Title:="Useful message"
Capturing the User Response
You can display different buttons and capture which one was clicked in a variable - for example:
Dim ButtonClicked As VbMsgBoxResult
ButtonClicked = MsgBox( _
Prompt:="Click a button", _
Buttons:=vbYesNo)
The VBA Input Box
You can use the VBA InputBox function to capture basic text inputs - for example:
Dim SomeText As String
SomeText = InputBox( _
Prompt:="Type in some text")
You can assign a default value to an input box - for example:
Dim SomeText As String
SomeText = InputBox( _
Prompt:="Type in some text", _
Default:="Some text")
You can capture non-string values, such as dates and numbers, from an input box - for example:
Dim MyNumber As Double
MyNumber = InputBox( _
Prompt:="Type in a number")
It's safer to capture all input box return values as strings and then test if they can be treated as another type - for example:
Dim MyString As String
Dim MyNumber As Double
MyString = InputBox( _
Prompt:="Type in a number")
If IsNumeric(MyString) Then
MyNumber = CDbl(MyString)
End If
The Excel Input Box
You can use the Excel-specific InputBox function to ask users for input - for example:
Dim SomeText As String
SomeText = Application.InputBox( _
Prompt:="Type in some text")
You can use the Type parameter to set the type of value returned by this input box - for example:
Dim MyNumber As Double
MyNumber = Application.InputBox( _
Prompt:="Type in a number", _
Type:=1)
You can select a cell while the Excel input box is displayed to capture the contents of the cell - for example:
Dim CellValue As String
CellValue = Application.InputBox( _
Prompt:="Select a cell")
You can capture a reference to a range object by setting the Type to 8:
Dim r As Range
Set r = Application.InputBox( _
Prompt:="Select cells", _
Type:=8)
You can build a formula by setting the Type to 0:
Dim MyFormula As String
MyFormula = Application.InputBox( _
Prompt:="Create a formula", _
Type:=0)
ActiveCell.FormulaLocal = MyFormula
This page contains reference material for the code used in this module.
The MsgBox Function
The MsgBox function has five parameters, although you're only ever likely to use the first three:
Parameter | Description |
---|---|
Prompt | The only compulsory parameter, the Prompt sets the text of the message that appears. |
Buttons | A misleadingly-named, optional parameter which allows you to control which buttons appear below the prompt, as well as which button is selected by default, and which icon is displayed next to the prompt. If you omit this, the message box displays no icon and will have a single OK button. |
Title | An optional parameter for setting the title text of the message. If you omit this, the title will be set to the name of the application; in Excel this is Microsoft Excel. |
HelpFile | An optional parameter for specifying the path to a custom help file saved in the .chm format. Writing custom help files is beyond the scope of this course. |
Context | An optional parameter related to the HelpFile. Again, this is beyond the scope of this course. |
Message Box Icons
You can display one of four icons on a message box:
Constant | Icon | Sample code |
---|---|---|
vbCritical | ![]() |
MsgBox "Something went wrong!", vbCritical |
vbExclamation | ![]() |
MsgBox "This might be a bad idea!", vbExclamation |
vbInformation | ![]() |
MsgBox "Just FYI", vbInformation |
vbQuestion | ![]() |
MsgBox "Are you sure?", vbQuestion |
Message Box Buttons
You can display six different combinations of buttons on a message box:
Constant | Buttons |
---|---|
vbAbortRetryIgnore | ![]() |
vbOKCancel | ![]() |
vbOKOnly | ![]() |
vbRetryCancel | ![]() |
vbYesNo | ![]() |
vbYesNoCancel | ![]() |
VbMsgBoxResult Values
A message box can return one of seven different results.
Constant | Value |
---|---|
vbOK | 1 |
vbCancel | 2 |
vbAbort | 3 |
vbRetry | 4 |
vbIgnore | 5 |
vbYes | 6 |
vbNo | 7 |
VBA InputBox Function
The VBA InputBox function has seven parameters, although you're only ever likely to use the first three:
Parameter | Description |
---|---|
Prompt | The only compulsory parameter, the Prompt sets the text that appears on the input box. |
Title | This sets the text that appears in the title bar of the input box. If you omit this, the title will be set to the name of the application; in Excel this is Microsoft Excel. |
Default | This allows you to populate the input box with a value as soon as it appears. The user can either accept this value or type over it with one of their own. |
XPos | A number representing the distance, in a measurement unit called twips (a twentieth of a point!), from the left hand edge of the screen to the left hand edge of the input box. |
YPos | A number representing the distance in twips from the top of the screen to the top of the input box. |
HelpFile | An optional parameter for specifying the path to a custom help file saved in the .chm format. Writing custom help files is beyond the scope of this course. |
Context | An optional parameter related to the HelpFile. Again, this is beyond the scope of this course. |
Excel InputBox Function
The Excel InputBox function (Application.InputBox) has eight parameters:
Parameter | Description |
---|---|
Prompt | The only compulsory parameter, the Prompt sets the text that appears on the input box. |
Title | This sets the text that appears in the title bar of the input box. If you omit this, the title will be set to the name of the application; in Excel this is Microsoft Excel. |
Default | This allows you to populate the input box with a value as soon as it appears. The user can either accept this value or type over it with one of their own. |
XPos | A number representing the distance, in a measurement unit called twips (a twentieth of a point!), from the left hand edge of the screen to the left hand edge of the input box. |
YPos | A number representing the distance in twips from the top of the screen to the top of the input box. |
HelpFile | An optional parameter for specifying the path to a custom help file saved in the .chm format. Writing custom help files is beyond the scope of this course. |
Context | An optional parameter related to the HelpFile. Again, this is beyond the scope of this course. |
Type | An optional parameter to set the return type of the input box. The default value is 2 which sets the return type to a String. |
Excel InputBox Return Types
The Excel input box can be set to return one of seven different data types:
Number passed to Type parameter | Type returned |
---|---|
0 | A formula |
1 | A number |
2 | A string (the default) |
4 | A Boolean value |
8 | A Range object |
16 | An error value |
64 | An array of values |
Try doing one or more of the following exercises for this module:
Exercise 5.01 Exercise 5.02 Exercise 5.03Test how well you've learnt the topics in this module.
Begin your test