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


excel formulas sumif examplesbasic vba programmingmicrosoft excel project schedule templateexcel vba save as pdfdelete empty rows excelhow do you unhide in excelexcel vba sql querylookup function excel 2013cellcolortypes of variables vbaunprotect excel sheet without passwordpmo templates excel3d bubble chart excelvlookup demovbscript output to text filetask timeline templatems excel match functionselection.pastespecialvisual basic codingsexcel vba convert column number to letterhow to delete macros in excelneat excel tricksexcel macro not workingdml in sqlactivex examplevlookup function excel 2010excel pivotchartvba excel color codesexcel vba with selectioncreate a worksheet in excelhow do i filter out duplicates in exceldeveloper tab in excel 2007how do you hide columns in excelhide excel worksheetremove blank cells in excel formuladeveloping dashboardsgraph wizardvba excel variablesumif syntaxexcel 2010 iferroroffset function in excel 2010excel macro concatenateupdate screen vbavba coding examplesconversion of xml file to excelpivot table setupmicrosoft access vba programming for the absolute beginnerexcel format hyperlinkvlookup rangehow to unhide a tab in excelsql dcl commandsadvanced oops interview questionsproject portfolio dashboard excelrecording excel macrossumif function excel 2013scheduling template for excelexcel identify duplicates in a columnexcel worksheet tabexcel clear formattingmessagebox syntaxhow to unhide multiple rows in excelvba yes novba excel tutorialvba excel worksheetmacro formula excelhow to break password in excel sheeteliminating duplicates in excelcell address vbaarray lookup excelexcel duplicate cellexcel vba documentationhow to insert macros in excelvba symbolexcel vba list files in foldervba array of stringsprint vba codevba excel delete columnvbs file existshyperlink shortcut in excelsave vba excelvbscript msgbox examples