Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Module 5 - Messages and User Inputs

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

5.3 - The VBA Input Box
5.3.1 - Capturing the User Input
5.3.2 - Setting a Default Value
5.3.3 - Cancelling an Input Box
5.3.4 - Capturing Numbers/Dates
5.3.5 - Type Mismatch Errors
5.3.6 - Using Variant Variables

5.4 - The Excel Input Box
5.4.1 - Using the Excel Input Box
5.4.2 - Setting the Return Type
5.4.3 - Cancelling the Input Box
5.4.4 - Controlling Number Types
5.4.5 - Capturing Dates
5.4.6 - Selecting Cell Contents
5.4.7 - Forcing Cell Selection
5.4.8 - Returning a Range Object

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

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

Begin your test

This page has 0 threads

Add a new post

MODULES

LESSONS

Displaying Messages

Capturing the User Response

The VBA Input Box

The Excel Input Box

The MsgBox Function

Message Box Icons

Message Box Buttons

VbMsgBoxResult Values

VBA InputBox Function

Excel InputBox Function

Excel InputBox Return Types

Head office

London

Manchester