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!

Module 5 - Messages and User Inputs

Lesson 5.3 - The VBA Input Box

Topic 5.3.1 - Capturing the User Input

An input box is a dialog box that allows a user to enter a value which your code can capture and make use of. There are two types of input box you can use in Excel VBA: a generic VBA input box; and an Excel-specific version. This section shows you how to work with the former type.

A basic VBA input box allows the user to type in a value.

Files Needed

You don't need any files for this part of the lesson.

Completed Code

You can click here to download a workbook containing the completed code.

You can ask for basic text input from the user with the InputBox function. In a new workbook, insert a module and create a subroutine called BasicInputBox. Add code to display an input box as shown below:

The InputBox function has seven parameters in total but only the Prompt is required.

Use the Prompt parameter to ask the user a question, or provide an instruction.

Hopefully your users will be capable of following simple instructions such as this one.

You can run the code to display the input box:

It's a little "no frills" but it will suffice for now.

You can customise the Title of an input box in the same way as for a message box:

Here we're using continuation characters (a space and underscore) and named arguments.

Running the code shown above produces an input box similar to this:

Sadly, there's little else you can do to alter the appearance of the input box.

Capturing the Result

Asking for input isn't much use unless you capture what the user has entered!

Unless you store the value typed in, when you click OK your program will instantly forget what your name is (just as I often do after greeting delegates at the start of a training course).

Create a new subroutine called CaptureInputBoxResult.

Finding the Data Type Returned by an Input Box

Before attempting to capture the result of an input box, it's worth finding out what type of value the InputBox function returns. To do this, you can type inputbox followed by a space to display a tooltip:

The tooltip indicates that the type of value returned by the InputBox function is a String.

As the VBA InputBox function returns a string, you could capture its result in a string variable:

Declare a string variable in the subroutine.

Capturing the Result

To return the result of the input box to the variable you have declared, you make the variable equal to the input box:

Assign the input box to the variable. Make sure you enclose the arguments of the input box in a set of parentheses.

Debugging to Check the Result

You can step through the procedure using the F8 key and enter a value when the input box appears:

You don't have to take the instruction quite so literally.

After clicking OK, you can use the Locals window to see that the value has been captured in the variable:

The value you have entered appears in the Locals window.

Using the Result of the Input Box

You can now use the value you have captured in other parts of the procedure:

This simple procedure displays the value that was entered back to the user.

After running the procedure, typing in a value and clicking OK you'll see a message box appear:

The message simply displays the value you typed into the input box.

To practise displaying input boxes:

Using any workbook, create a new module and add a subroutine called AskMeAnything.
Add code to display an input box which asks the user an important question:

Sub AskMeAnything()

InputBox _

"What's the best pizza topping?"

End Sub

Alter the input box code so that it displays a customised title:

Sub AskMeAnything()

InputBox _

"What's the best pizza topping?", _

"Best Pizza"

End Sub

Run the subroutine to check the appearance of the input box:

Try entering a value and clicking OK. Your code won't capture the value you entered yet, but we'll get to that soon!

Add a second input box which asks a related question:

Sub AskMeAnything()

InputBox _

"What's the best pizza topping?", _

"Best Pizza"

InputBox _

"What's the worst pizza topping?", _

"Worst Pizza"

End Sub

Run the subroutine again and check that the second input box appears after you have closed the first one:

Yuck.

Save and close the workbook.

To practise capturing basic text input:

Using any workbook, insert a module and create a subroutine called HelloYou.
Declare a variable which can hold a string value:

Sub HelloYou()

Dim YourName As String

End Sub

Add code to display an input box which asks the user their name. Store the result of the input box in the variable you have declared:

Sub HelloYou()

Dim YourName As String

YourName = InputBox( _

"What's your name?")

End Sub

Display a message which greets the user with the name they have entered:

Sub HelloYou()

Dim YourName As String

YourName = InputBox( _

"What's your name?")

MsgBox "Hello " & YourName

End Sub

Run the subroutine to check that you see a sensible message:

Your message will only be as sensible as the name you type in, of course.

Add a second variable to hold the user's title. Use another input box to populate this variable and then concatenate its value into the final message:

Sub HelloYou()

Dim YourName As String

Dim YourTitle As String

YourName = InputBox( _

"What's your name?")

YourTitle = InputBox( _

"Enter your title")

MsgBox "Hello " & YourTitle & " " & YourName

End Sub

Run the subroutine and enter some appropriate values:

You could enter something less extravagant.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Capturing the Result

Finding the Data Type Returned by an Input Box

Capturing the Result

Debugging to Check the Result

Using the Result of the Input Box

Head office

London

Manchester