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.

Basic inputbox

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:

InputBox function

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.

Enter name

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

 

You can run the code to display the input box:

Basic 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:

Prompt and title

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:

Basic inputbox

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!

Ask for name

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:

Inputbox return

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 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:

Capture result

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:

Enter value

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:

Locals window

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:

Show on message

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:

Message

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

 

To practise displaying input boxes:

  1. Using any workbook, create a new module and add a subroutine called AskMeAnything.
  2. 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

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

  1. Run the subroutine to check the appearance of the input box:
Basic inputbox

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

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

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

Yuck.

 
  1. Save and close the workbook.

To practise capturing basic text input:

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

Sub HelloYou()

 

Dim YourName As String

 

End Sub

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

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

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

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

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

  1. Run the subroutine and enter some appropriate values:
Final message

You could enter something less extravagant.

 
  1. Save and close the workbook.
This page has 0 threads Add post