To create user input box by VBA Excel.
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.
- 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
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 “Interaction” class ,while “Interaction” class falls in VBA library. We can see the same in image below.
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.
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.
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.
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
Post you may like