Excel VBA InputBox with Number Only Criteria

Get FREE Advanced Excel Exercises with Solutions!

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.

📝 Note: By default, the Developer tab remains hidden. You can learn to enable the Developer tab by following this linked article.

Moving to the developer tab and clicking on the visual basic button

Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.

Inserting module in the visual basic for applications window

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
ARGUMENT REQUIRED/OPTIONAL EXPLANATION
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.

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.

VALUE OF TYPE ARGUMENT DESCRIPTION 
0 Formula
1 Number
2 Text String
4 Boolean (True/False)
8 Range / Cell Reference
16 #N/A
64 Array

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

Dataset showing average high temperature in Fahrenheit for US states and cities.

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.

Converting the input temperature from degree Fahrenheit to degree Celsius

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.

VBA code for converting the temperature from Fahrenheit to Celsius

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

Code Breakdown:

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


1.2 Calculating Area of Circle

List of planets and their radius in kilometers

Assuming the Radiusof Planets dataset in the B4:C13 cells which contain a list of Planets and their Radius in km respectively.

Calculating the area of circle using the input radius

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.

VBA code for calculating the area of circle

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

Dataset showing the rank, company name, and revenue earned by top 15 companies.

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.

Computing the sum from the input column number

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.

VBA code for calculating the sum of the range

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

Code Breakdown:

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


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

Returning the company name and revenue from the input rank.

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.

VBA code for returning the company name and revenue from the input rank

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

Code Breakdown:

  • 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: InputBox with Default Value


2.2 Calculating Average of a Range of Numbers from InputBox

Computing the average from the chosen range

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.

VBA code for calculating the average of the selected range

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

Code Breakdown:

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


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.

Overview of UserForm inserting multiple inputs into worksheet cell

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.

Inserting UserForm from the Visual Basic window

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

Clicking view code option

Now, select any of the Controls like Label, TextBox, etc. >> Right-click to go to the View Code.

Running vba code to run userform

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.

Storing number value into worksheet cell from inputbox

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.

VBA code for storing number value into worksheet cell from inputbox.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo