EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Message Functions VBA InputBox
 

VBA InputBox

Madhuri Thakur
Article byMadhuri Thakur

VBA InputBox in Excel

Excel VBA InputBox

Most of the time, you use the data that is already with you; sometimes, you come up with a situation where you want the user to input the data like Name, Age, etc., kind of personal information. These kinds of input information are needed sometimes when we are conducting a survey and need to review the unbiased opinion of people.

 

 

Using Excel VBA’s InputBox, we can get the input data from the user. As the name suggests, InputBox works as a pop-up box which asks the user to feed certain information.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The syntax for VBA InputBox

Following is the syntax for VBA InputBox:

Syntax

Where,

  • Prompt – Message that gets displayed to the user. This is the only required argument; the rest other arguments are optional.
  • Title – The heading appears on the dialog window after the successful execution of the InputBox statement. If not given, by default system prints ‘Microsoft Excel’ as a title.
  • Default – It is the default value that comes on the dialog box. We can keep it null as well. No default value set for this argument.
  • XPos – Position coordinate of a dialog box on the X-axis.
  • YPos – Position coordinate of a dialog box on the Y-axis.
  • HelpFile – Location of the help file that should be used. This argument becomes mandatory to set when the ‘Context’ argument is passed.
  • Context – Represents Help ContextId for the HelpFile used. Mandatory to use when ‘HelpFile’ argument is passed.
  • Out of all these arguments, only the first three are enough to successfully create an InputBox.

Creating InputBox in Excel VBA

Below are the different steps to create InputBox in Excel using VBA Code.

You can download this VBA InputBox Excel Template here – VBA InputBox Excel Template
  • Open VBE (Visual Basic Editor by pressing Alt + F11 simultaneously in an Excel file and click on Insert and add a new Module in VBE.

Insert Moadule

  • Create a macro in this module named ‘Module1’. Assign a name to the macro.

VBA InputBox Example 1-1

  • Type command InputBox in the editor.

VBA InputBox Example 1-2

Give the following inputs to the InputBox statement:

  • Prompt: “May I Know Your Full Name?”
  • Title: “Personal Information”
  • Default: “Start Typing Here

Code:

Sub InputBoxEX()

InputBox "May I know Your Name?", "Personal Information", "Start Typing Here"

End Sub

VBA InputBox Example 1-3

  • Press F5 or run the button to run this code.

VBA InputBox Example 1-4

How to Store the Output of InputBox to Cells?

You have created the InputBox to get the input from the user. However, where the output will get stored? We haven’t mentioned any location where the output can be stored.

Let’s store the output we get from InputBox to excel cells by following the below steps in VBA:

  • Declare a new variable ‘Name’ with type as ‘Variant’. This type of variable can take any value (numeric/string/logical etc.).

Code:

Sub InputBoxEX()

Dim Name As Variant

End Sub

VBA InputBox Example 2-1

  • Use InputBox to assign a value to this variable called ‘Name’.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here")

End Sub

VBA InputBox Example 2-2

If you could have noticed the parenthesis after the InputBox statement, those are needed because we are using this statement for a variable ‘Name’.  As soon as it becomes a value to be given for a variable,  it has to be mentioned in a proper format with parenthesis.

  • Now whatever value the user has typed in the dialog box, we want that to see in cell A1 of the Excel sheet. Put the following statement for the same in VBE: Range(“A1”).Value = Name.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here")

Range("A1").Value = Name

End Sub

VBA InputBox Example 2-3

This is it; now, let’s run this code and see how it works.

  • Click on the Run button or press F5 to run this code; you’ll get the following dialog box. Write your name in the dialog box that popped up with the name “Personal Information” and hit OK to see where the output gets printed.

VBA InputBox Example 2-4

  • As soon as you input the value and click on OK, you can see the value inputted in cell A1. See the screenshot below.

VBA InputBox Example 2-5

Any value can be stored using InputBox if the variable is properly defined. In this case, you have defined the variable ‘Name’ as ‘Variant’. The variant data type can take any data value, as I said earlier.

See the example below:

I am giving a number as an argument to dialog box when it pops up. See as below:

Example 2-6

Click on OK, see the output below:

Example 2-7

The value in cell A1 is changed to 2019.

Now let’s change the variable type to Date.

Code:

Sub InputBoxEX()

Dim Name As Date

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here")

Range("A1").Value = Name

End Sub

Example 2-8

Run the code and try to input the value other than the date. I will enter my name itself and click OK.

Example 2-9

  • After clicking OK, a run time error message saying, ‘Type mismatch’.

It occurred because the type of variable name is Date now, and I have given an input argument other than date value (a string name). Due to which this code doesn’t execute and throws an error.

Validation of User Input

What if I tell you that the user input can be restricted? Yes, It’s true! You can restrict the user input to Characters, Numbers or Logical, etc.

To restrict the user input, you can use the Application.InputBox.

The syntax for Application.InputBox is as follows:

Syntax for Application.InputBox

Where,

Prompt – Message that gets popped up for the user.

Title – Heading of the dialog box.

Default – Default value that pops up on typing area under dialog box.

Type – The type of input.

These are the only important arguments which are enough to run this statement.

Let’s start this through example.

  • Declare a variable Name as a Variant.

Code:

Sub InputBoxEX()

Dim Name As Variant

End Sub

VBA InputBox Example 2-1

Assign Application.InputBox to the variable called Name with the same arguments as those you have used InputBox. i.e. Prompt, Title and Default. See the code below:

Code:

Sub InputBoxEX()

Dim Name As Variant

Name=Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here")

End Sub

VBA InputBox Example 3-2

Now, put comma 5 times to ignore the Left, Top, HelpFile and HelpContextID. After 5 commas,  you can specify the input type.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here",,,,,

End Sub

Example 3-3

Type of the input string have below mentioned validations:

Types of Input Strings

  • Let’s choose 1 as a Type in our statement. It means only Numbers/Numeric Values are acceptable in the dialog box which pops up.

Code:

Sub InputBoxEX()

Dim Name As Variant

Name = Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here",,,,,1)

End Sub

Example 3-4

  • Run the code manually or using the F5 key and give name as an input in the typing area as shown below. Then click OK and see the Output.

Example 3-5

It says the number is not valid. That seems logical because we have set the variable input type as the number and provide the text as an input that is not acceptable.

In this way, you can restrict the user to enter only the values which you want to see through InputBox.

Things to Remember

  • InputBox accepts up to 255 arguments and can display only 254. So be cautious about the max length a user can enter.
  • The application method can be used to set the input data type. However, if it is not used, then you need to be more specific about the input data type.
  • A variant data type is recommended to choose as it can hold any of Numeric/Text/Logical etc. B=Values.

Recommended Articles

This has been a guide to VBA InputBox. Here we discussed how to create InputBox in Excel using VBA code along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VBA Get Cell Value
  2. VBA VLOOKUP
  3. VBA Input
  4. VBA RGB

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA InputBox Excel Template

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW