Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
This module shows how you can display message boxes on screen, and use input boxes to get information from users of your programs.
5.1 - Displaying Messages |
---|
5.1.1 - Displaying a Simple Message |
5.1.2 - Using Multiple Lines |
5.1.3 - Displaying Icons |
5.1.4 - Changing the Title |
5.2 - Capturing the User Response |
---|
5.2.1 - Displaying Different Buttons |
5.2.2 - Capturing the Result |
5.2.3 - Testing the Result |
5.2.4 - Setting a Default Button |
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.
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"
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)
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
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 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. |
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 |
You can display six different combinations of buttons on a message box:
Constant | Buttons |
---|---|
vbAbortRetryIgnore | |
vbOKCancel | |
vbOKOnly | |
vbRetryCancel | |
vbYesNo | |
vbYesNoCancel |
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 |
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. |
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. |
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 testKingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.