User Input in VBA
Learn how to accept input from users, display messages, validate values, and use input in Excel automation with VBA.
1. What Is User Input?
User input allows a VBA program to receive information while it is running. Instead of hardcoding every value, a macro can ask the user to enter a name, number, quantity, or other value.
The common VBA functions used for basic interaction are
InputBox and MsgBox.
2. InputBox Function
The InputBox function displays a dialog box that
asks the user to enter a value. The entered value is returned
as a string.
Syntax
InputBox(prompt, title, default)
- prompt: Message displayed to the user.
- title: Optional title of the dialog box.
- default: Optional default input value.
Example 1: Accept a name
Sub GetStudentName()
Dim studentName As String
studentName = InputBox( _
"Enter your name:", _
"Student Details")
MsgBox "Welcome, " & studentName & "!"
End Sub
When the macro runs, the user enters a name. VBA stores the
returned text in studentName and displays a
welcome message.
InputBox returns an empty string, just like
submitting an empty input. Use suitable validation when
these cases need to be distinguished.
3. MsgBox Function
The MsgBox function displays a message to the user.
It can show information, warnings, questions, and buttons.
Syntax
MsgBox(prompt, buttons, title)
| Button or Icon | Purpose |
|---|---|
vbOKOnly |
Displays an OK button. |
vbYesNo |
Displays Yes and No buttons. |
vbYesNoCancel |
Displays Yes, No, and Cancel buttons. |
vbInformation |
Displays an information icon. |
vbExclamation |
Displays a warning icon. |
vbQuestion |
Displays a question icon. |
Example 2: Display a message
Sub ShowMessage()
MsgBox "Your data has been saved successfully.", _
vbInformation, _
"Success"
End Sub
Example 3: Ask for confirmation
Sub ConfirmAction()
Dim answer As VbMsgBoxResult
answer = MsgBox( _
"Do you want to continue?", _
vbYesNo + vbQuestion, _
"Confirmation")
If answer = vbYes Then
MsgBox "You selected Yes."
Else
MsgBox "You selected No."
End If
End Sub
MsgBox can be stored in a variable and checked
to determine which button the user selected.
4. Accept Numeric Input
Basic InputBox input is text. Before performing
calculations, validate the input using IsNumeric
and convert it to the appropriate numeric type.
Example 4: Add two numbers
Option Explicit
Sub AddTwoNumbers()
Dim firstValue As String
Dim secondValue As String
Dim firstNumber As Double
Dim secondNumber As Double
firstValue = InputBox("Enter the first number:")
If Len(Trim$(firstValue)) = 0 Then
MsgBox "First input is empty or cancelled.", vbExclamation
Exit Sub
End If
If Not IsNumeric(firstValue) Then
MsgBox "Please enter a valid first number.", vbExclamation
Exit Sub
End If
secondValue = InputBox("Enter the second number:")
If Len(Trim$(secondValue)) = 0 Then
MsgBox "Second input is empty or cancelled.", vbExclamation
Exit Sub
End If
If Not IsNumeric(secondValue) Then
MsgBox "Please enter a valid second number.", vbExclamation
Exit Sub
End If
firstNumber = CDbl(firstValue)
secondNumber = CDbl(secondValue)
MsgBox "Sum = " & (firstNumber + secondNumber), vbInformation
End Sub
Explanation: The macro checks that both
inputs are not empty and are numeric before converting them
with CDbl and calculating the sum.
5. Validate User Input
Validation checks whether the input meets the requirements before the program processes it.
Example 5: Validate age
Option Explicit
Sub ValidateAge()
Dim ageText As String
Dim age As Long
ageText = InputBox("Enter your age:")
If Len(Trim$(ageText)) = 0 Then
MsgBox "Age was not entered.", vbExclamation
Exit Sub
End If
If Not IsNumeric(ageText) Then
MsgBox "Enter a numeric age.", vbExclamation
Exit Sub
End If
If CDbl(ageText) <> Fix(CDbl(ageText)) Then
MsgBox "Age must be a whole number.", vbExclamation
Exit Sub
End If
If CDbl(ageText) < 0 Or CDbl(ageText) > 120 Then
MsgBox "Enter an age between 0 and 120.", vbExclamation
Exit Sub
End If
age = CLng(ageText)
MsgBox "Entered age: " & age, vbInformation
End Sub
This example checks for empty input, non-numeric values, decimal values, and ages outside the allowed range.
6. Accept Input and Write to Excel Cells
VBA can take input from a user and place the result directly into a worksheet cell.
Example 6: Enter an employee name
Option Explicit
Sub EnterEmployeeName()
Dim employeeName As String
employeeName = InputBox("Enter employee name:")
If Len(Trim$(employeeName)) = 0 Then
MsgBox "Employee name was not entered.", vbExclamation
Exit Sub
End If
ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = employeeName
MsgBox "Employee name saved in cell A2.", vbInformation
End Sub
| Cell | Result Example |
|---|---|
| A2 | Rahul Patil |
Ensure that a worksheet named Sheet1 exists
in the workbook containing the macro.
7. InputBox vs Application.InputBox
VBA provides a basic InputBox function. Excel also
provides Application.InputBox, which can restrict
the expected input type.
| Input Method | Typical Use |
|---|---|
InputBox |
Accepting simple text input. |
Application.InputBox |
Accepting values such as numbers or selecting a range. |
Example 7: Accept a number
Option Explicit
Sub GetNumberUsingApplicationInputBox()
Dim result As Variant
result = Application.InputBox( _
Prompt:="Enter a number:", _
Title:="Numeric Input", _
Type:=1)
If VarType(result) = vbBoolean Then
If result = False Then
MsgBox "Input was cancelled.", vbInformation
Exit Sub
End If
End If
MsgBox "You entered: " & result, vbInformation
End Sub
Application.InputBox,
pressing Cancel returns Boolean False. The
Variant variable allows the result to be checked
before it is used.
8. Common Mistakes to Avoid
-
Assuming input is numeric:
validate using
IsNumericbefore conversion. -
Ignoring empty input:
check
Len(Trim$(value))where blank input is not allowed. -
Confusing Cancel and blank input:
the basic
InputBoxreturns an empty string for both cases. - Using the wrong data type: convert values to an appropriate type before calculations.
- Using a missing worksheet: verify worksheet names before writing to cells.
9. Practice Exercises
Write VBA macros to solve the following tasks.
- Accept a student's name and display a welcome message.
- Accept two numbers and display their sum.
- Accept a product price and quantity and calculate the total.
- Accept a student's marks and validate that they are between 0 and 100.
- Ask the user to confirm an operation using Yes and No buttons.
- Accept an employee name and save it in cell A2.
- Accept a number and display whether it is positive, negative, or zero.
- Use
Application.InputBoxto accept a numeric value.
Important Points to Remember
InputBoxaccepts basic user input as text.MsgBoxdisplays information and can return a button selection.- Use
IsNumericto validate numeric input. - Use
CDblor another suitable conversion function before calculations. - Handle empty input and cancellation appropriately.
- Use
Application.InputBoxwhen typed input or range selection is needed.
Summary
User input makes Excel VBA macros interactive. The
InputBox function collects values, while
MsgBox displays messages and collects simple button
choices. Validate input before using it in calculations, handle
empty values and cancellation, and use worksheet references to
transfer data between users and Excel.
These concepts prepare you to create more useful macros with conditional statements, loops, and dynamic ranges.