Module 5 - Messages and User Inputs

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.03
 

Test how well you've learnt the topics in this module.

Begin your test
This page has 0 threads Add post