REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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

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.

Inputbox-Number

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.

Inputbox

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.

Inputbox-Text

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.

Inputbox-Boolean-True

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.

Inputbox-Boolean-False

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.

Inputbox-Boolean-Error

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.

Inputbox-Range

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

One Comment

  1. Samuel September 3, 2014 at 2:51 AM

    Where are the last two options for the “Type”? I would sure find those valuable additions.

Leave A Comment