VBA InputBox Options in Excel to Accept different Formats of Data

VBA InputBox Options in Excel to Accept different types, options and Formats of Data from the user. Examples Macros and syntax commands are provided here to accept text, number, formula, cell reference or a logical value. VBA InputBox function is useful to accept data from the user. Using MessageBox we can show the message to the user.And We can recieve information like Yes, No or Cancel from the user using Message Box. when we want to collect a specific information from user, it is not possible with MessageBox.We can use InputBox in such type of situations. You can accept any data from the user, it can be either text, number, formula, cell reference or a logical value.

VBA InputBox – Solution(s):

InpuBox will diplay a dialog box for user to enter data. It contains text box to accept inputs from the user.
It will accept all type of data like number, text etc. By default InputBox returns string data type as a output.

Syntax:

InputBox(Prompt

[, Title][, Default][, Left][, Top][, HelpFile][, HelpContextID][, Type])
InputBox contains several arguments.

Where
Prompt – The message to be displayed in the dialog box.
Title – The title for the InputBox.(Optional)
Default – It will specifies a value that will appear in the text box.(Optional)
Left & Top – It Specifies a position of the dialog box.Default center of screen.(Optional)
HelpFile – The name of the Help file.(Optional)
HelpContextId – The context Id number of the Help topic in Help file.(Optional)
Type – It specifies the return data type.Default returns text data type.(Optional)

Note: The following table lists the values that can be passed in the type argument.So that we can decide what an InpuBox is supposed to return based on type(8th Argument)

Type Value	Type Description
0	For formula – If you want to accept a formula from the user you can use 0 as a type
1	For number – If you want to accept a number from the user you can use 1 as a type
2	For Text (a string) – If you want to accept a text string from the user you can use 2 as a type
4	For logical value (True or False) – If you want to accept a boolean value from the user you can use 4 as a type
8	For cell reference – If you want to accept a range from the user you can use 8 as a type
16	For an error value – If you want to accept an error value like #N/A from the user you can use 16 as a type
64	For an array of values – If you want to accept an array of values from the user you can use 64 as a type

InputBox in Excel VBA to Accept Values from User – Example Cases:

• Accept a formula from the User using InputBox
• Accept a number from the user using InputBox
• Accept a text string from the user using InputBox
• Accept a boolean value from the user using InputBox
• Accept a range from the user using InputBox
• Accept an error value like #N/A from the User using InputBox
• Accept an array of values from the User using InputBox

Accept a formula from the User using InputBox

Using InputBox Function, you can accept a Formula from the user. Please find the following code and example.

Accept a Number from the User using InputBox

Using InputBox Function, you can accept a Number from the user. Please find the following code and example. In this example when you enter number it will diplay entered number as a output using msgbox. Otherwise, it will display as ‘Number is not valid’ as a output. And, It will highlight the user to reinter the number in specified place.

Code:

Sub InputBox_Type1()
    'Variable declaration
    Dim iNum As Integer
    
    ' Accept Number from the user
    iNum = Application.InputBox("Please Enter Your favourate Number:", "Example: Accept Number", , , , , , 1)
    
    MsgBox "My favourite Number is: " & iNum, , "Type1 Example"
    
End Sub

Output: Example – 1

When we run above code, it will ask user to enter the number. You can see in the step1. You can enter five in the input box. You can see in the step2. And finally click on ok button in step2. you will see the output in step3.

Output: Example – 2

When we run the above code, it will ask user to enter the number. You can see in the step1. You can enter any text in the input box to check the output. You can see in the step2. It will display message like ‘Number is not valid’ in step3. Why because it will accept numbers only. Once click on OK button it will highlight the entered text. It’s shown in step4. Now you can enter number in the input box. You can see in step5. And finally you can see output in step6.

Accept a text string from the user using InputBox

Using InputBox Function, you can accept a text string from the user. Please find the following code and example. In this example when you enter text it will diplay entered text as a output using msgbox.

Code:

Sub InputBox_Type2()
    'Variable declaration
    Dim iNum As Integer
    
    'Accept Text from the user
    sName = Application.InputBox("Please Enter Text:", "Type2 Example", , , , , , 2)
    MsgBox "Entered Text is: " & sName, , "Type2 Example"

End Sub

Output:

Please find the above code and ran it to check for output. It will ask user to enter the text. You can see in the step1. You can enter text like ‘Analysistabs’ in the input box. You can see in the step2. And finally click on ok button in step2. You will see the output in step3.

Accept a boolean value from the user using InputBox

Using InputBox Function, you can accept a Boolean value from the user. Please find the following codes and examples. You can enter either True/False or 1/0 as a Boolean value. Otherwise it will display the message like ‘Logical value not found’.

Sub InputBox_Type4_Ex1()

    'Accept Boolean Value from the user
    blnAns = Application.InputBox("You are VBA Expert, is it True?" & vbCr & "If Yes- Enter True Otherwise Enter False ", "Type4 Example", , , , , , 4)
    
    If blnAns = True Then
        MsgBox "Grate! You are VBA Expert, You can learn Advanced Our VBA"
    Else
        MsgBox "You can Star Learning from Basics"
    End If
    
End Sub

'OR

Sub InputBox_Type4_Ex2()

    'Accept Boolean Value from the user
    blnAns = Application.InputBox("You are VBA Expert, is it True?" & vbCr & "If Yes- Enter 1 Otherwise Enter 0  ", "Type4 Example", , , , , , 4)
    If blnAns = 1 Then
        MsgBox "Grate! You are VBA Expert, You can learn Advanced Our VBA"
    Else
        MsgBox "You can Star Learning from Basics"
    End If

End Sub

Output: Example – 1

Please find the above code and ran it to check for the output. It will ask user to enter the Boolean value. It is either True or 1. Or you can enter either False or 0. You can see in step1. You can enter Boolean value ‘True’ in the input box. It’s shown in step2. Finally it will generate output like ‘Great! You are VBA Expert, You can learn Advanced VBA’ in the step3.

Output: Example – 2

As shown in the above example1, Instead of ‘True’ enter ‘False’. Now you can see the output like ‘You can start learning from Basics’ in the step3.

Output: Example – 3

Please find the above code and ran it to check for the output. It will ask user to enter the Boolean value. It is either True or 1. Or you can enter either False or 0. In this example we can see message like ‘Logical Value is not valid’. It will show this message when we enter other than Boolean value in inputbox. This, you can see in step3. Once you click on OK button it will highlight the entered text. It’s shown in step4. Now you can enter Boolean value in the input box. You can see in step5. And finally you can see the output in step6.

Accept a range from the user using InputBox

Using InputBox Function, you can accept a range from the user using inputbox. Please find the following codes and examples. You can enter the formula in the inputbox in the following way.

Sub InputBox_Type8()
    
    'Accept Range from the user
    Sheet1.Activate
    Set sCell = Application.InputBox("Select a Cell from the Sheet1:", "Type8 Example", , , , , , 8)
    MsgBox "Selected Cell Address :" & sCell.Address, vbOKOnly, "Cell Address"

End Sub

Output:

Please find the above code to accept range to the inputbox. It will ask user to select a cell or range from the sheet. Select cell or range from sheet as shown in step2. And then click on ok button to see the cell or range reference as output. You can see the output in step3.