In VBA, the InputBox function allows you to prompt the user to input some value(s) during the execution of a code. It’s important to note that you may have inadvertently referred to it as the “Input function,” but in VBA, the commonly used function to get user input is InputBox.

Basic syntax

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

• prompt (required): The message to be displayed in the dialog box.
• title (optional): The title of the dialog box. If omitted, the application name is used.
• default (optional): The default value that appears in the input field. If omitted, the field is blank.
• xpos and ypos (optional): The position on

Example

Sub GetUserInput()
    Dim userInput As String
    
    ' Prompt the user for input
    userInput = InputBox("Please enter some information", "User Input Required")
    
    ' Check if the user clicked Cancel or did not enter anything
    If userInput = "" Then
        MsgBox "You either clicked Cancel or entered nothing", vbInformation
    Else
        ' Do something with the input
        MsgBox "You entered: " & userInput, vbInformation
    End If
End Sub

This simple subroutine called GetUserInput prompts the user to input some data when the code runs. If the user enters some information and clicks OK, that information is stored in the userInput variable and can be used throughout the subroutine. If the user clicks Cancel or the user input is empty, the subroutine shows an informational message box saying that no input was detected.

Here’s a breakdown of the InputBox arguments:

• The prompt (“Please enter some information”) the message displayed to the user.
• The title (“User Input Required”) the title of the input box window (optional).

The InputBox function returns a string, so if you expect a number, you will need to convert it accordingly using functions like CInt, CLng, CSng, CDbl, etc. depending on the type of number:

Sub GetUserNumericInput()
    Dim userInput As String
    Dim numericInput As Double
    
    userInput = InputBox("Please enter a number", "Numeric Input Required")
    
    ' Check if the user entered a number and clicked OK
    If IsNumeric(userInput) And userInput <> "" Then
        numericInput = CDbl(userInput)
        ' Do something with the numeric input
        MsgBox "You entered the number: " & numericInput, vbInformation
    Else
        MsgBox "You either clicked Cancel or entered an invalid number", vbInformation
    End If
End Sub

In this example, the subroutine first checks if the user’s input is a numeric value and then converts it to a Double using CDbl before using it.

Remember to include appropriate error handling if you’re expecting numeric input since the user might enter non-numeric characters which will cause a runtime error when converting to a number if not handled properly.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project