Excel VBA InputBox with Number Only Criteria

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.

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.

• Syntax

InputBox(Prompt, [Title], [Default], [Xpos], [Ypos], [Helpfile], [Context])

• InputBox Arguments

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.

Syntax

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.

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

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: VBA InputBox for Integer Value Only in Excel

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: Excel VBA: InputBox Type with Examples

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

You can see the video where entering the column number, in this case, 4, and get the sum of the values.

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

The video below shows all the steps described in this example.

Read More: Excel VBA: InputBox with Default Value

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

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.

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: Vbscript InputBox with Multiple Fields in Excel

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

Pros:

• 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.

Cons:

• 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.

Wrapping Up

• 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.

Download Practice Workbook

You can download the following practice workbook to practice yourself.

Conclusion

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.

Related Articles

• Excel VBA: Custom Input Box
• Excel VBA: InputBox Date Format