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
550 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 ...
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.
You don't need any files for this part of the lesson.
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.
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.
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.
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.
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.
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:
Sub AskMeAnything()
InputBox _
"What's the best pizza topping?"
End Sub
Sub AskMeAnything()
InputBox _
"What's the best pizza topping?", _
"Best Pizza"
End Sub
Try entering a value and clicking OK. Your code won't capture the value you entered yet, but we'll get to that soon!
Sub AskMeAnything()
InputBox _
"What's the best pizza topping?", _
"Best Pizza"
InputBox _
"What's the worst pizza topping?", _
"Worst Pizza"
End Sub
Yuck.
To practise capturing basic text input:
Sub HelloYou()
Dim YourName As String
End Sub
Sub HelloYou()
Dim YourName As String
YourName = InputBox( _
"What's your name?")
End Sub
Sub HelloYou()
Dim YourName As String
YourName = InputBox( _
"What's your name?")
MsgBox "Hello " & YourName
End Sub
Your message will only be as sensible as the name you type in, of course.
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
You could enter something less extravagant.
Kingsmoor 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.