VBA InputBox: Complete Guide with Validation Examples (2025)

Q: Can InputBox accept numbers?

InputBox always returns a string. Convert to numbers using Val(), CInt(), CLng(), or CDbl(). Check validity with IsNumeric() first. Example: Dim num As Integer: num = CInt(InputBox("Enter number:")).

Q: How do I set a default value in InputBox?

Use the third parameter (default). Example: InputBox("Enter name:", "Input", "John Doe"). The default value appears in the text field and can be edited or accepted as-is.

Updated: January 2025•8 min read

Quick Answer: InputBox displays a dialog box for user input. Syntax: InputBox(prompt, title, default). Returns entered text as a string. Always validate input and check for Cancel (empty string).

What is InputBox?

InputBox is a built-in VBA function that displays a dialog box with a text field, prompting users to enter data. It returns the entered value as a string, making it perfect for collecting user input like names, numbers, dates, or any text-based information.

Simple Example:

Sub GetUserName()
    Dim userName As String
    
    userName = InputBox("Please enter your name:", "User Input")
    
    If userName <> "" Then
        MsgBox "Hello, " & userName & "!"
    Else
        MsgBox "No name entered."
    End If
End Sub

InputBox Features:

✓Collects user input
✓Returns entered text as string
✓Has OK and Cancel buttons
✓Can set default values
✓Customizable title and prompt

InputBox vs MsgBox:

•InputBox: Gets user input
•MsgBox: Shows messages
•InputBox: Has text field
•MsgBox: Only has buttons
•InputBox: Returns text
•MsgBox: Returns button clicked

InputBox Syntax

InputBox(prompt, [title], [default], [xpos], [ypos], [helpfile], [context])

Parameters:

Parameter	Required	Description
prompt	Yes	Message displayed in the dialog box
title	No	Title bar text (default: "Microsoft Excel")
default	No	Default value in text field
xpos	No	Horizontal position (rarely used)
ypos	No	Vertical position (rarely used)

Basic Usage (Prompt Only)

userInput = InputBox("Enter your age:")

With Title

userInput = InputBox("Enter your age:", "Age Input")

With Default Value

userInput = InputBox("Enter your age:", "Age Input", "25")

Complete Example

Sub GetUserInfo()
    Dim userName As String
    Dim userAge As String
    
    ' Get name with default
    userName = InputBox("Enter your name:", "User Information", "John Doe")
    
    ' Check if user clicked Cancel
    If userName = "" Then
        MsgBox "Operation cancelled."
        Exit Sub
    End If
    
    ' Get age
    userAge = InputBox("Enter your age:", "User Information")
    
    ' Display results
    MsgBox "Name: " & userName & vbNewLine & "Age: " & userAge
End Sub

Input Validation

Always validate InputBox data since users can enter anything or click Cancel. Check for empty strings, validate data types, and handle errors gracefully.

1. Check for Cancel (Empty String)

Sub CheckCancel()
    Dim userInput As String
    
    userInput = InputBox("Enter value:")
    
    ' User clicked Cancel or entered nothing
    If userInput = "" Then
        MsgBox "No input provided."
        Exit Sub
    End If
    
    ' Process input
    MsgBox "You entered: " & userInput
End Sub

2. Validate Numeric Input

Sub ValidateNumber()
    Dim userInput As String
    Dim numValue As Double
    
    userInput = InputBox("Enter a number:")
    
    ' Check if cancelled
    If userInput = "" Then Exit Sub
    
    ' Check if numeric
    If Not IsNumeric(userInput) Then
        MsgBox "Please enter a valid number!", vbExclamation
        Exit Sub
    End If
    
    ' Convert to number
    numValue = CDbl(userInput)
    MsgBox "Number entered: " & numValue
End Sub

3. Validate with Loop (Re-prompt)

Sub ValidateWithLoop()
    Dim userInput As String
    Dim isValid As Boolean
    
    Do While Not isValid
        userInput = InputBox("Enter a number between 1 and 100:")
        
        ' Check if cancelled
        If userInput = "" Then Exit Sub
        
        ' Validate
        If IsNumeric(userInput) Then
            If CDbl(userInput) >= 1 And CDbl(userInput) <= 100 Then
                isValid = True
            Else
                MsgBox "Number must be between 1 and 100!", vbExclamation
            End If
        Else
            MsgBox "Please enter a valid number!", vbExclamation
        End If
    Loop
    
    MsgBox "Valid input: " & userInput
End Sub

4. Validate Date Input

Sub ValidateDate()
    Dim userInput As String
    Dim dateValue As Date
    
    userInput = InputBox("Enter a date (mm/dd/yyyy):")
    
    If userInput = "" Then Exit Sub
    
    ' Check if valid date
    If IsDate(userInput) Then
        dateValue = CDate(userInput)
        MsgBox "Date entered: " & Format(dateValue, "mmmm dd, yyyy")
    Else
        MsgBox "Invalid date format!", vbExclamation
    End If
End Sub

5. Validate String Length

Sub ValidateLength()
    Dim userInput As String
    
    userInput = InputBox("Enter password (min 8 characters):")
    
    If userInput = "" Then Exit Sub
    
    ' Check length
    If Len(userInput) < 8 Then
        MsgBox "Password must be at least 8 characters!", vbExclamation
        Exit Sub
    End If
    
    MsgBox "Password accepted."
End Sub

💡 Validation Best Practices:

•Always check for empty string (Cancel button)
•Use IsNumeric(), IsDate() before converting
•Provide clear error messages
•Use loops to re-prompt for valid input
•Set appropriate default values

Practical Examples

Example 1: Calculate Discount

Sub CalculateDiscount()
    Dim priceInput As String
    Dim discountInput As String
    Dim price As Double
    Dim discount As Double
    Dim finalPrice As Double
    
    ' Get price
    priceInput = InputBox("Enter original price:", "Discount Calculator")
    If priceInput = "" Or Not IsNumeric(priceInput) Then Exit Sub
    price = CDbl(priceInput)
    
    ' Get discount percentage
    discountInput = InputBox("Enter discount % (0-100):", "Discount Calculator", "10")
    If discountInput = "" Or Not IsNumeric(discountInput) Then Exit Sub
    discount = CDbl(discountInput)
    
    ' Calculate
    finalPrice = price - (price * discount / 100)
    
    ' Display result
    MsgBox "Original: $" & Format(price, "0.00") & vbNewLine & _
           "Discount: " & discount & "%" & vbNewLine & _
           "Final Price: $" & Format(finalPrice, "0.00")
End Sub

Example 2: Search and Highlight

Sub SearchAndHighlight()
    Dim searchTerm As String
    Dim cell As Range
    Dim foundCount As Integer
    
    searchTerm = InputBox("Enter text to search:", "Search")
    If searchTerm = "" Then Exit Sub
    
    ' Clear previous highlights
    Cells.Interior.ColorIndex = xlNone
    
    ' Search and highlight
    For Each cell In Range("A1:Z100")
        If InStr(1, cell.Value, searchTerm, vbTextCompare) > 0 Then
            cell.Interior.Color = RGB(255, 255, 0)  ' Yellow
            foundCount = foundCount + 1
        End If
    Next cell
    
    MsgBox "Found " & foundCount & " matches for '" & searchTerm & "'"
End Sub

Example 3: Create New Worksheet

Sub CreateNewSheet()
    Dim sheetName As String
    Dim ws As Worksheet
    
    ' Get sheet name
    sheetName = InputBox("Enter name for new worksheet:", "Create Sheet")
    
    ' Validate
    If sheetName = "" Then
        MsgBox "Operation cancelled."
        Exit Sub
    End If
    
    ' Check if sheet exists
    On Error Resume Next
    Set ws = Worksheets(sheetName)
    On Error GoTo 0
    
    If Not ws Is Nothing Then
        MsgBox "Sheet '" & sheetName & "' already exists!", vbExclamation
        Exit Sub
    End If
    
    ' Create sheet
    Set ws = Worksheets.Add
    ws.Name = sheetName
    
    MsgBox "Sheet '" & sheetName & "' created successfully!"
End Sub

Example 4: Email Validator

Sub ValidateEmail()
    Dim email As String
    Dim isValid As Boolean
    
    Do While Not isValid
        email = InputBox("Enter email address:", "Email Validation")
        
        If email = "" Then Exit Sub
        
        ' Basic email validation
        If InStr(email, "@") > 0 And InStr(email, ".") > InStr(email, "@") Then
            isValid = True
            Range("A1").Value = email
            MsgBox "Email saved: " & email
        Else
            MsgBox "Invalid email format! Please include @ and domain.", vbExclamation
        End If
    Loop
End Sub

Frequently Asked Questions

What is InputBox in VBA?

InputBox is a VBA function that displays a dialog box prompting users to enter data. It returns the entered value as a string. Syntax: InputBox(prompt, title, default, xpos, ypos, helpfile, context). Use it to collect user input like names, numbers, or dates.

What's the difference between InputBox and MsgBox?

InputBox collects user input and returns a value. MsgBox displays information and returns which button was clicked. InputBox has a text field for typing; MsgBox only has buttons. Use InputBox to get data, MsgBox to show messages.

How do I validate InputBox input?

Check if the returned value is empty (user clicked Cancel), use IsNumeric() for numbers, IsDate() for dates, and Len() for length. Use loops to re-prompt until valid input is received. Example: If Not IsNumeric(userInput) Then MsgBox "Please enter a number".

Can InputBox accept numbers?

InputBox always returns a string. Convert to numbers using Val(), CInt(), CLng(), or CDbl(). Check validity with IsNumeric() first. Example: Dim num As Integer: num = CInt(InputBox("Enter number:")).

How do I set a default value in InputBox?

Use the third parameter (default). Example: InputBox("Enter name:", "Input", "John Doe"). The default value appears in the text field and can be edited or accepted as-is.

What happens if user clicks Cancel on InputBox?

InputBox returns an empty string (""). Always check for this: If userInput = "" Then Exit Sub. This prevents errors when users cancel instead of entering data.

Can I use InputBox for passwords?

No, InputBox displays typed characters visibly. For passwords, use a UserForm with a TextBox control and set PasswordChar property to "*". InputBox is not suitable for sensitive data.

How do I create multi-line InputBox?

VBA InputBox doesn't support multi-line input. For multi-line text, create a UserForm with a multi-line TextBox (set MultiLine property to True). InputBox is limited to single-line input.

Need Help Writing VBA Code?

Our AI-powered VBA Code Generator creates working VBA code from plain English descriptions. No coding knowledge required!

✓ No credit card required ✓ 5 free generations ✓ Instant results

VBA MsgBox

Learn how to display messages and get user responses with VBA MsgBox function.

VBA Variables

Learn about VBA variables, data types, and variable declaration.