VBA InputBox Options in Excel to Accept different Formats of Data

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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.

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.

LIMITED TIME OFFER
By |January 13th, 2013|Excel VBA|1 Comment

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

One Comment

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

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

Leave A Comment


Related pages


remove duplicates in excel 2003formula for countifopen xlsx with excel 2003excel consolidateexcel remove sheet protectioninstr in excelshort cut keys in ms excelvba buttonvba istextvba typesexcel formula correctordashboards in excel templatescountif tutorialexcel vba offset functiondelete duplicate rowshow to apply filters in excelpv table excelhow to remove hyperlink in excelvba select worksheetcool macrosexcel formulas for nameshow do i delete empty rows in excelvba syntax guidemultiple if then statements in excel 2010trim in vbaexcel sheet shortcutsconcatenate in excelexcel macro exercisesfinding duplicates excel3d graphing in excelexcel vba with rangevba open access database from excelvba excel progress barradio buttons excelvba excel subhow to do the vlookup in excel 2010excel vba data typesuseful excel macrosvba getrowsinsert new worksheet excel 2007copy sheet to another workbookproject schedule template xlsmacros tutorialhow to merge worksheets in excelvba application calculationstatistical chart typesvba save excelvba for beginnershow do you find duplicates in excelcombine multiple worksheets into one worksheetexcel formulas with examplesortable columns excelexcel multiple conditionsrun excel macroexcel vba iserrorvb 6.0 interview questions and answersmsg box vbadelete sheet vbavba access databaseusing the sumif function in excelexcel formula isna vlookupvba operatorsexcel weeks between two datessoftware development cost estimation templatevba isnumexcel array lookupuse of vlookup function in excelexcel unprotect sheet vbasimple vba programhow to remove blank rows in excel 2007excel dashboard pluginvba excel ebookhow to find double entry in excel 2007excel vba unprotect workbookfor each loop in vbavba chart axisexcel hide worksheetlookup excel examplecontoh vba excelvba serverproject tracker template excelsumif exampledefinition of row and column in ms excelpowerpoint meeting agenda template