InputBox in Excel VBA is a helpful tool for receiving user input. Sometimes it is necessary to limit the input to a specific data type, like numbers. In this article, we’ll take a brief introduction to the InputBox function and the Application.InputBox method, with their syntaxes and parameters. Then, we’ll explore some practical examples of the InputBox function and the Application.InputBox method. Now, this article is all about creating an Excel VBA InputBox with Number Only Criteria.
Moreover, we’ll also demonstrate an alternative to InputBox i.e. UserForm to accept multiple inputs from the user. Eventually, we’ll answer some of the frequently asked questions regarding InputBoxes, look at the pros and cons, and get some helpful tips regarding InputBox.
The above video is an overview of this article, which represents how to restrict InputBox to only number in Excel VBA. Here, the user enters the radius of the planet in the InputBox while the program calculates the area and shows the result in the F5 cell.
Download Practice Workbook
You can download the following practice workbook to practice yourself.
How to Launch and Insert Code in Visual Basic Editor in Excel
The Developer tab contains the VBA applications including creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc. This is our starting point in the world of VBA; just follow along.
Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.
This launches the Microsoft Visual Basic for Applications window. Now, click the Insert tab and choose Module from the list. Afterward, we get a small Module window to insert our VBA code, and we hit the Run button or F5 key to execute the code.
The advantage of inserting the code in the Module is that we can apply this code to all the worksheets in this Excel workbook. Conversely, we can make our code only available for a specific worksheet.
Introduction to InputBox Function in Excel VBA
The InputBox function in Excel VBA displays a dialog box where the user can enter data. The function then returns the entered data in the dialog box.
InputBox(Prompt, [Title], [Default], [Xpos], [Ypos], [Helpfile], [Context])
- InputBox Arguments
|Prompt||Required||The text displayed in the dialog box.|
|[Title]||Optional||The text displayed in the InputBox’s title bar|
|[Default]||Optional||The text displayed in the InputBox’s title bar|
|[Xpos]||Optional||Indicates the X-axis (horizontal) positioning location of the InputBox.|
|[Ypos]||Optional||Indicates the Y-axis (vertical) positioning location of the InputBox.|
|[HelpFile]||Optional||Provides a user-friendly assistance file.|
|[Context]||Optional||Indicates the HelpContextId in the ‘HelpFile’.|
Introduction to Application.InputBox Method in Excel VBA
The Application.InputBox method does the same task as the InputBox functions with some added perks like
- Defining the return data types and default values.
- Identifying the press of the Cancel button.
- Determining a False return value and more customizable options.
- Better error handling.
Application.InputBox(Prompt, [Title], [Default], [Left], [Top], [Helpfile], [HelpContextID], [Type])
InputBox Type Arguments
The Application.InputBox method has a similar set of arguments to the InputBox function with one exception, that is the Type argument. The Type argument is optional and defines the returned data type. In case of its absence, the Application.InputBox method returns text by default.
|VALUE OF TYPE ARGUMENT||DESCRIPTION|
|8||Range / Cell Reference|
Excel VBA InputBox with Number Only Criteria: 2 Examples with Cases
In this portion, we’ll discuss five examples of how to restrict InputBox to number only in Excel VBA. For each method, we’ll start with a brief description of the dataset, where applicable, then display an overview of the result including the VBA code explanation, and lastly show a video demonstration of executing the VBA code. So, let’s see each example in detail with the appropriate illustration.
1. Using InputBox Function to Take Number Only
Let’s apply the InputBox function to validate the entry of numbers only in the simple examples described below.
1.1 Converting Temperature from Fahrenheit to Celsius
Let’s suppose the List of Temperatures in March dataset in the B4:D13 cells contains a list of States, Cities, and the Average High in °F columns respectively.
In this case, we’ll enter a temperature in Fahrenheit in the InputBox while the VBA code converts this value to Celsius and returns the result in the G5 cell. As a note, the InputBox handles the entry of invalid data like blanks, strings, etc., and keeps asking the user to enter numeric data.
Now, to apply this VBA code follow the steps shown previously to insert a Module. To do this, insert the code into the Module window >> press the Run button or hit the F5 key.
Sub convert_temp() 'define variables and data types Dim user_input As Variant Dim fahrenheit As Double Dim celsius As Double 'run Do loop until user enters numeric value Do user_input = InputBox("Please enter a temperature in Fahrenheit:") If Not IsNumeric(user_input) Then MsgBox "Please enter a valid number." End If Loop Until IsNumeric(user_input) 'convert Fahrenheit to Celsius fahrenheit = user_input celsius = Round((fahrenheit - 32) * 5 / 9, 1) 'return result in G5 cell Range("G5").Value = celsius End Sub
- First, declare the sub-procedure named convert_temp.
- Next, define the variables user_input, Fahrenheit, and Celsius and assign Variant and Double data types respectively.
- Second, the Do While loop keeps asking for the temperature input using the InputBox function until the user enters a valid numeric value.
- This input value is stored in the user_input variable.
- Then, the IsNumeric function checks whether the input is a numeric value. If it is not, then the MsgBox function displays an error message prompting the user to enter a valid number.
- Third, convert the temperature from Fahrenheit to Celsius and use the Round function to round the value to 1 decimal place.
- Lastly, store the answer in the Celsius variable and return it to the G5 cell.
The cell reference G5 can be changed to any other cell accordingly. Additionally, we can adapt the code to convert from feet to meters, kilometers to miles, etc.
The video below shows the steps to convert the input temperature from Fahrenheit to Celsius.
Read More: Excel VBA: InputBox Type with Examples
1.2 Calculating Area of Circle
Assuming the Radiusof Planets dataset in the B4:C13 cells which contain a list of Planets and their Radius in km respectively.
With this code, we can compute the area of a circle by entering the radius as an input and the code automatically returns the output.
This code can also handle the entry of invalid data and continue prompting the user to enter numeric data. In a similar fashion, we can also obtain the area of a rectangle, square, etc.
Sub planet_area() 'define variables and data types Dim user_input As Variant Dim radius As Double Dim area As Double 'run Do loop until user enters numeric value Do user_input = InputBox("Please enter the radius of the planet:") If Not IsNumeric(user_input) Then MsgBox "Please enter a valid number." End If Loop Until IsNumeric(user_input) 'calculate area radius = user_input area = 3.14 * radius * radius 'return result in F5 cell Range("F5").Value = area End Sub
This code is similar to the previous method, so check out the above explanation. The exception here is that it asks for a radius in kilometers and applies the formula to calculate the area of a circle.
If you wish, you can alter the cell reference of the F5 cell.
The embedded video demonstrates the process to calculate the area of the circle from the radius input by the user.
Read More: How to Use Excel VBA InputBox with Example
1.3 Computing Sum of a Range by Entering Column Number in InputBox
Considering the Revenue Earned by Top 15 Companies dataset shown in the B4:D19 cells, which contains the Rank, Company name, and the Revenue earned columns.
Here, the InputBox asks the user to enter the column number, since we want to get the sum of the Revenue column, we’ve entered 4 and the program generates in the D21 cell.
If the user enters any invalid data, the code returns a warning message to the user.
Sub sum_range() 'define variables and data types Dim total As Double Dim lastRow As Long 'enter column number colNum = InputBox("Enter column number: ") 'Check if the user clicked "Cancel" or entered a non-numeric value If IsNumeric(colNum) = False Then MsgBox "Invalid input!" Exit Sub End If 'convert string to integer colNum = CInt(colNum) 'determine last row in the column lastRow = Cells(Rows.count, colNum).End(xlUp).Row 'calculate sum of the range total = WorksheetFunction.sum(Range(Cells(1, colNum), _ Cells(lastRow, colNum))) 'return result in D21 cell Range("D21").Value = total End Sub
- The InputBox function asks the user for the column number which is stored in the colNum variable.
- The If Then statement checks for invalid entries and returns a warning message to the user.
- The CInt function converts the string colNum into an integer.
- Next, determine the last row of the chosen column using the Cells(Rows.Count, colNum) property.
- Here, the End(xlUp) method moves up from the last cell until it reaches the last row of data in the column.
- Afterward, using the Sum function calculates the sum of the cells from the first to the last row, and returns the output to the D21 cell.
You can see the video where entering the column number, in this case, 4, and get the sum of the values.
Read More: How to Handle VBA InputBox Cancel Button in Excel (2 Examples)
2. Implementing Application.InputBox Method to Take Number Only
In this portion, we’ll use the Application.InputBox method in some practical examples where the user can enter numbers only.
2.1 Implementing VLookup Function to Return Match from InputBox
In this situation, the dialog box prompts the user to input the rank which ranges from 1 to 15, and the VBA code delivers the company name and the revenue earned in the MsgBox.
In contrast to the previous examples, we’ve applied the Application.InputBox method which allowed us to restrict the input to numeric data by setting the Type:=1 argument.
Sub return_match_InputBox() 'set range for the dataset Set rng = Range("B5:D19") 'enter a rank from 1 to 15 rnk = Application.InputBox( _ Prompt:="Enter a rank from 1 to 15: ", _ Title:="Top 15 Company", Type:=1) 'handling blank and invalid entries If rnk = False Then MsgBox "Invalid entry", vbOKOnly, "Top 15 Company" ElseIf rnk < 1 Or rnk > 15 Then MsgBox "Enter a rank from 1 to 15", vbOKOnly, "Top 15 Company" Else 'vlookup to return company name and country comp = Application.WorksheetFunction.VLookup(rnk, rng, 2, False) revenue = Application.WorksheetFunction.VLookup(rnk, rng, 3, False) MsgBox "Company ranking: " & rnk & vbLf & "Company name: " & comp & _ vbLf & "Revenue (Billions): " & revenue, vbOKOnly, "Top 15 Company" End If End Sub
- To begin with, define the subprocedure return_match_InputBox and use the Set statement to store the B5:D19 which represents the dataset.
- Setting Type to 1, display an InputBox to prompt the user to enter a rank from 1 to 15. The InputBox function returns the value entered by the user and assigns it to the variable rnk.
- Following this, check if the user entered a valid rank. If the user cancels the InputBox, rnk will be False and a MsgBox will appear with the message Invalid entry. If the user enters a rank that is less than 1 or greater than 15, a MsgBox appears with the message Enter a rank from 1 to 15.
- Finally, if the user enters a valid rank, then the VLookup function looks up the company name and revenue associated with the entered rank. A MsgBox is then displayed with the company ranking, name, and revenue.
The video below shows all the steps described in this example.
Read More: Excel VBA: Custom Input Box (7 Examples)
2.2 Calculating Average of a Range of Numbers from InputBox
For one thing, we can also choose a range of cells and perform a calculation on them, for example, here we chose the D5:D19 cells and the program computed the average revenue of the companies.
This time we set the Type argument to 8 in the Application.InputBox method which allows the user to enter a range of cells or cell references.
Sub average_range() 'define variables and data types Dim sum As Double Dim count As Integer Dim average As Double 'enter range into InputBox Set rng = Application.InputBox("Select the range to average: ", _ "Calculating Average", , , , , , 8) 'use For loop to sum values and count their numbers sum = 0 count = 0 For Each cell In rng.Cells If IsNumeric(cell.Value) Then sum = sum + cell.Value count = count + 1 End If Next cell 'handle blank entries If count = 0 Then MsgBox "There are no valid numbers in the specified range." Else 'calculate average average = sum / count 'return result in D21 cell Range("D21").Value = average End If End Sub
- Initially, use the Set statement to store the B5:D19 which represents the dataset.
- The InputBox asks the user to enter a cell reference since we’ve entered Type:=8 and the input is assigned to the rng variable.
- A For Next loop iterates through each cell in the range. If a cell contains a numeric value, its value is added to the sum, and the count is incremented.
- Alternately, if the number of valid cells is 0, a MsgBox displays there are no valid numbers in the range.
- Finally, if there are valid cells, the average is calculated by dividing the sum by the count and the Range method returns the output to the D21 cell.
At this point, watch the video which recaps all the steps to run this code.
I hope these examples gave you a clear idea to restrict an InputBox to number only with Excel VBA.
Read More: Vbscript InputBox with Multiple Fields in Excel (2 Examples)
How to Make VBA UserForm with Multiple Inputs in Excel (InputBox Alternative)
In truth, entering multiple inputs into the InputBox can become a challenge since it can display a limited amount of information. Luckily, there is a nifty solution to this problem i.e. we can construct a UserForm where the user can enter multiple inputs and the data can be returned in a worksheet.
According to the above image, we can enter the Employee ID, Name, Age, Role, and Location information and press the Enter button to record this entry into the worksheet. Later, we can press the Reset button to clear the fields and enter new data.
First of all, launch the Visual Basic window >> click the Insert tab >> choose UserForm.
Rename the Caption in the Properties table to “Survey”. Select the Frame option >> insert the Frame into the UserForm >> rename the Frame to “Employee Survey” as shown in the video.
Choose Label and insert it >> single click on the Label to rename it to “Employee ID” >> change the background color and set the text alignment to center.
Select TextBox >> Insert it into the UserForm beside the corresponding Label.
Similarly, insert 4 more Labels and 2 more TextBoxes.
Insert two ComboBoxes for the “Role” and “Location” labels.
Insert three CommandButtons >> rename them as “Enter”, “Reset” and “Cancel”.
Now, select any of the Controls like Label, TextBox, etc. >> Right-click to go to the View Code.
Copy and paste the VBA code into the window >> press Run to execute the code.
Private Sub Frame1_Click() End Sub Private Sub Label4_Click() End Sub Private Sub Label5_Click() End Sub Private Sub TextBox2_Change() End Sub Private Sub TextBox3_Change() End Sub Private Sub UserForm_Initialize() ComboBox1.List = Array("Seattle", "California", "Texas", "New York") ComboBox2.List = Array("Software", "IT", "Cyber Security", "Operations") End Sub Private Sub CommandButton1_Click() lastRow = Sheet13.Cells(Rows.count, "B").End(xlUp).Row + 1 Cells(lastRow, 2).Value = UserForm1.TextBox3.Value Cells(lastRow, 3).Value = UserForm1.TextBox1.Value Cells(lastRow, 4).Value = UserForm1.TextBox2.Value Cells(lastRow, 6).Value = UserForm1.ComboBox1.Value Cells(lastRow, 5).Value = UserForm1.ComboBox2.Value End Sub Private Sub CommandButton2_Click() UserForm1.TextBox1.Value = "" UserForm1.TextBox2.Value = "" UserForm1.TextBox3.Value = "" UserForm1.ComboBox1.Value = "" UserForm1.ComboBox2.Value = "" End Sub Private Sub CommandButton3_Click() Unload UserForm1 End Sub
Follow the instructions shown in this video to execute the UserForm and enter the input data into the worksheet cells.
Read More: Excel VBA: Input Box with Options
How to Store Number Value of InputBox to Cell in Excel VBA
Conversely, we can also store the number value of InputBox into worksheet cells, so just follow along.
This code displays an InputBox where the user can enter numeric data, in this the temperature in Fahrenheit, after pressing ok this value is inserted into the D5 cell.
In this case, we’ve chosen to enter the data in column D which is represented by the number 4, you can change this to return the data in a different column.
Sub store_num_val() 'define variables and data types Dim temp As Variant Dim row_num As Long 'Prompt the user to enter a number temp = InputBox("Enter temperature in Fahrenheit: ") 'Check if the user clicked "Cancel" or entered a non-numeric value If IsNumeric(temp) = False Then MsgBox "Invalid input!" Exit Sub End If 'Find the last used row in column D row_num = Cells(Rows.count, 4).End(xlUp).Row 'Store the user's input in the next available row in column D Range("D" & row_num + 1).Value = temp End Sub
Observe the steps in the video to store the numeric inputs into the worksheet cells.
Frequently Asked Questions (FAQs)
- What is the difference between InputBox Function Vs Application.InputBox Method in Excel VBA?
The InputBox function prompts the user to enter data into a pop-up dialog box stored as a text string. In contrast, the Application.InputBox method is more versatile because it can be applied to Excel objects, formulas, etc. Moreover, it can handle erroneous values and validate the user’s input.
- How do I restrict TextBox to enter only numbers in VBA?
By using the TextBox control’s KeyPress event to track when a key is pushed. Here, the event determines if a numeric digit or a special key and ensures that the TextBox only accepts a numerical input. From the ActiveX Controls insert a TextBox, then double-click the TextBox and insert this code.
Private Sub Textbox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 8 And KeyAscii <> 46 Then KeyAscii = 0 End If End Sub
- How to assign a value from InputBox Inside a variable in VBA?
We can declare a variable to contain the value and then assign the value to the variable in VBA to store the value that the user enters into an InputBox. In the following example code, the user is asked to enter a number, and the value is then saved in the “myNum” variable:
Sub StoreInputBoxValue() Dim myNum As Double myNum = InputBox("Please enter a number:") MsgBox "You entered the number " & myNum End Sub
- Can I set a minimum and maximum value for the numeric input?
Indeed, by adding conditions to our code that verifies whether the user-entered number falls within the appropriate range. For instance, we can apply the If Then statement to set the minimum and maximum values for the numeric input. Here, the “rnk” variable accepts values between 1 and 15 only.
Sub MinMax If rnk < 1 Or rnk > 15 Then MsgBox "Enter a rank from 1 to 15", vbOKOnly, "Top 15 Company" End If End Sub
- Can I format the numeric input to display a specific number format?
The VBA Format function enables us to define the number format to display a numeric input as a currency, percentage, etc.
Sub FormatNum Dim user_input As Variant user_input = InputBox("Enter a number:", "Number Input") If IsNumeric(user_input) Then MsgBox Format(user_input, "$#,##0.00") End If End Sub
use the “0.00%” format to display values as a percentage.
Pros and Cons of Restricting InputBox to Number Only in Excel
- Improved data accuracy: ensure that the VBA code works with accurate and valid data.
- Efficient coding: simplifies the VBA code and lowers errors.
- Better user experience: provides a user-friendly interface guiding them to enter the correct input.
- Limited functionality: limits the flexibility of user input, which may be unsuitable in many scenarios.
- Increased complexity: requires a clear and basic understanding of VBA, which may be challenging for beginners.
- Potential for user frustration: If the user is unaware that the input is restricted to only numbers, they may become frustrated when attempting to enter other data types.
Things to Remember
- By default, the InputBox function returns a string data type, which means that any data entered by the user will be treated as text.
- You can use the Application.InputBox method to prompt the user to enter data into a cell, rather than a pop-up dialog box.
- To ensure that the user enters only numeric data, you can use the IsNumeric function to check the data entered by the user.
- You can assign the value entered by the user to a variable and use it in your VBA code.
- You can use the Range object and the Value property to retrieve the numeric data entered by the user from a cell.
- The generic InputBox function displays a dialog box for the user to enter data, by default this data is stored as a string.
- In contrast, the Application.InputBox method performs a similar function with the additional ability to return a specified data type.
- The InputBox in general helps make an interactive, user-friendly program and can be used to perform simple calculations, like unit conversion, calculating area, summation, average, etc.
- An alternative to the InputBox is the UserForm which can take multiple inputs from the user and return an output.
In essence, displaying the Range Address using the MsgBox function in Excel VBA provides a powerful tool for verifying cell addresses, debugging, and improving the user experience. While there are some downsides to consider, such as the interruption of workflow and limited display options and interaction, in general, using the MsgBox function is worthwhile depending on the specific requirements.
We are hopeful that this article has provided you with a robust understanding of creating an Excel VBA InputBox with Number Only criteria. Armed with this knowledge, readers can now confidently incorporate number-only InputBox restrictions into their own VBA projects, improving the functionality and reliability of their code. In case, you have any suggestions or comments, don’t forget to share them with us.