How to Create an Excel VBA InputBox with Number Only Criteria (2 Methods)

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.

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

• Go to the Developer tab, then click on the Visual Basic button in the Code group.

• Click the Insert tab and choose Module from the list.
• In the Module window we insert our VBA code.
• To run the code, press the Run button or the 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.

Introduction to InputBox Function in Excel VBA

The InputBox function in Excel VBA displays a dialog box where the user can enter data. It then returns the entered data.

Syntax

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

InputBox Arguments

Introduction to Application.InputBox Method in Excel VBA

This method accomplishes the same task as InputBox but offers additional features:

• Defining return data types and default values.
• Identifying the Cancel button press.
• Customizable options and 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.

Method 1 – Using InputBox Function to Take Number Only

1.1 Converting Temperature from Fahrenheit to Celsius

Suppose you have a dataset in an Excel sheet where the B4:D13 cells contain information about States, Cities, and Average High temperatures in Fahrenheit.

You want to convert these Fahrenheit temperatures to Celsius using VBA code.

In this case, we’ll enter a temperature in Fahrenheit in the InputBox and the VBA code will convert this value to Celsius and returns the result in cell G5.

Note: The InputBox handles the entry of invalid data like blanks, strings, etc., and keeps asking the user to enter numeric data.

• To apply this VBA code, follow the steps shown previously to insert a Module.
• Insert the code below into the Module window >> press the Run button or press 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

Note: You can adapt the code to convert from feet to meters, kilometers to miles, etc.

Read More: VBA InputBox for Integer Value Only in Excel

1.2 Calculating Area of Circle

Assume you have another dataset in the B4:C13 cells, containing a list of Planets and their Radii in kilometers. You want to calculate the area of a circle based on the radius.

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

Note: You can adapt the code for other conversions (e.g., feet to meters) or geometric shapes.

Read More: Excel VBA: InputBox Type with Examples

1.3 Computing Sum of a Range by Entering Column Number

Suppose you have a dataset in an Excel sheet where the B4:D19 cells contain information about Company Ranks, Names, and Revenue Earned. You want to calculate the sum of the revenue column based on a user-entered column number.

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 it in cell D21.

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

Method 2 – Implementing Application.InputBox Method to Take Number Only

In this section, 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

Now let’s consider another scenario. You prompt the user to input a rank (ranging from 1 to 15), and the VBA code retrieves the corresponding company name and revenue earned.

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

Read More: Excel VBA: InputBox with Default Value

2.2 Calculating Average of a Range of Numbers from InputBox

Suppose you have an Excel dataset where the D5:D19 cells contain Revenue Values for different companies. You want to calculate the Average Revenue based on a user-entered range of cells.

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

How to Create aVBA UserForm with Multiple Inputs (InputBox Alternative)

Entering multiple inputs into the InputBox can become a challenge as it can display a limited amount of information. The solution to this problem is to 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.

• Create the UserForm
  • Open the Visual Basic for Applications (VBA) window in Excel.
  • Click the Insert tab and choose UserForm to create a new UserForm.
  • Rename the UserForm’s caption to Survey.
  • Add a Frame control to the UserForm and rename it to Employee Survey.
  • Insert Labels for each input field (e.g., Employee ID, Name, Age) and set their properties (background color, alignment).
  • Add TextBoxes for user input.
  • Insert ComboBoxes for the Role and Location labels.
  • Add three CommandButtons (Enter, Reset, Cancel).

• Write Code for UserForm
  • Right-click any control (e.g., Label, TextBox) and choose View Code.
  • Copy and paste the following VBA code into the UserForm’s code window:

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

• Run the UserForm
  • Press the Run button to execute the code.
  • The UserForm will appear, allowing users to enter data.
  • Click Enter to record the entry into the worksheet.
  • Use Reset to clear fields and enter new data.

Read More: Vbscript InputBox with Multiple Fields in Excel

How to Store Numeric Value from InputBox to Cell

To store a numeric value from an InputBox to a worksheet cell:

This code displays an InputBox where the user can enter numeric data.  The temperature in Fahrenheit is entered and after pressing OK the value is inserted into cell D5.

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

• Create a Subroutine
  • Insert the following VBA code:

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

• Run the Subroutine
  • Execute the store_num_val subroutine.
  • An InputBox will appear, allowing the user to enter a numeric value (temperature in Fahrenheit).
  • If valid, the value will be stored in the next available row in column D.

Frequently Asked Questions (FAQs)

• What is the difference between InputBox Function Vs Application.InputBox Method in Excel VBA?
  • InputBox Function
    • Prompts the user to enter data via a pop-up dialog box.
    • Returns the entered data as a string.
    • Limited customization options (e.g., only text input).
  • Application.InputBox Method
    • More versatile and flexible.
    • Can be applied to various Excel objects (e.g., cells, ranges, formulas).
    • Allows specifying input types (e.g., numeric, text, dates).
    • Handles invalid or erroneous input more effectively.

• How do I restrict TextBox to only enter numbers in VBA?

• • Use the KeyPress event of the TextBox control.
  • Example 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?

• • Declare a variable to store the value.
  • Example code:

    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?

• • Use an If Then statement to validate the input range.
  • Example code:

    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?

• • Use the Format function to display numeric values in a specific format (e.g., currency, percentage).
  • Example code:

    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

•  Pros and Cons of Restricting InputBox to Numbers Only
  • Pros:
    • Improved data accuracy.
    • Efficient coding.
    • Better user experience.
  • Cons:
    • Limited flexibility.
    • Increased complexity.
    • Potential user frustration if unaware of the restriction.

Key Points to Remember

1. InputBox Function:
   • By default, the InputBox function returns data as a string. This means that any input entered by the user is treated as text.
   • To prompt the user to enter data directly into a cell (instead of using a pop-up dialog box), you can use the Application.InputBox method.
2. Ensuring Numeric Input:
   • To ensure that the user enters only numeric data, you can use the IsNumeric function to validate the input.
   • Once validated, you can assign the user’s input to a variable and use it in your VBA code.
3. Retrieving Numeric Data:
   • To retrieve numeric data entered by the user from a cell, you can work with the Range object and its Value property.

In Summary

• The generic InputBox function provides a dialog box for user input, storing the data as a string by default.
• In contrast, the Application.InputBox method offers similar functionality but allows you to specify a desired data type.
• InputBox is useful for creating interactive, user-friendly programs and can handle tasks like unit conversion, area calculation, summation, and averages.
• Alternatively, consider using a UserForm to collect multiple inputs from the user and return an output.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

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