Create InputBox in VBA Excel -debugvba.com

input box vba excel

Objective

To create user input box by VBA Excel.

Approach

In this VBA Excel automation, we are creating the input box to get the user input. We shall showcase the functionality of InputBox method and InputBox Function both.

Some important features about InputBox [Method and Function]–

  • It is mandatory to declare the variable in which the input value will be stored. If we do not declare the variable, then code will throw an error. We can see the same in the image below that when we commented the variable declaration, code threw an error. We can see the same in image below.

 

input box vba excel

 

  • If we want to use InputBox method, we use “Application” as a qualifier. Then syntax will be “Application. InputBox”. We can see the details in the links provided in reference section

 

InputBox Function

In this case we can input anything, it can be an integer or string etc. It has a limitation in terms of data validation, according to which when we enter the input in input box it does not validate the type of data.

So, if someone enters a string and the expected input value was numeric then it will show error message, and we will have to handle the error. In such case we will have to re run the code from beginning, but in case of InputBox Method, data is validated and we can again enter the data.

InputBox function  is a member of “Interactionclass ,while “Interaction” class falls in VBA library. We can see the same in image below.

 

input box vba excel

 

Code For InputBox Function

 

'Use InputBox to input a Integer

Sub UserInputFunction()
Dim MyUserInput As Variant
MyUserInput = InputBox(Prompt:="Enter Financial Year", Title:="Financial Year", Default:=2019)
If MyUserInput = "" Then
MsgBox "No input provided"
Else
Range("A1").Value = MyUserInput
End If
 
End Sub

 

When we run the code mentioned above, an input box will be displayed. We have set the default value as 2019, which will be already filled when InputBox appears.If user clicks on Cancel button then it will display the message “No input provided”,we can see the same in “If” condition in the code above.

 

input box vba excel

 

InputBox Method

InputBox method is a member of Application class. It has the option to validate the type of data which is entered by user. If there is a mismatch in type of data entered and specified data type, then it will throw the error.

 

input box vba excel

 

We can validate the data while entering it in input box. If there is wrong type of data entered in input box, we can again enter the data. We can specify the input data type, it can be Numeric ,String ,Array , Formula etc.In the below code we were supposed to enter numeric data type(type=1), but we entered a string “Test_2019” to test the code.So the code threw an error as it was expecting a numeric value and not a string.We will have to enter a numeric value to execute the code ahead. See image below.

 

input box vba excel


Code For InputBox Method

'Use InputBox method to input a Range

Sub UserInputMethod()

Dim MyUserInput As Integer
 MyUserInput = Application.InputBox(Prompt:="Enter the year", Title:="FinancialYear", Default:=2019, Type:=1)
If MyUserInput = False Then
MsgBox "No input provided"
Else
Range("A1").Value = MyUserInput
End If

End Sub

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function
https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox

Post you may like

Create message Box in VBA Excel