VBA InputBox for Integer Value Only in Excel

Get FREE Advanced Excel Exercises with Solutions!

The InputBox method in Visual Basic for Applications (VBA) is a helpful way to collect user input from within an application. This article will explicitly describe how to create VBA InputBox for Integer Value Only in Excel. It enables programmers to ask consumers for information and then store the response in a variable for later use. Developers can define the intended input data type, such as integer, string, or date, when utilizing the InputBox function.  We’ll use the VBA InputBox function and then obtain the data for use in the VBA code.

In the above video, you can definitely see that after running the code, the prompted InputBox takes only an integer as input, it stores the data and sets those values in cells of Column D.


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 Excel VBA InputBox Function

As the name implies, we can easily display an InputBox to the viewers and request responses from them using the VBA InputBox function. Particularly, this is useful for obtaining a single input from the user.

Arguments of InputBox function

Objective:

This function returns an InputBox with some kind of criteria.

Syntax:

The syntax of this function is as follows:

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

Parameters or Arguments:

The arguments are defined as follows.

  • Prompt: This is the only required argument of this function. This text is displayed in the input box.
  • Title: It is optional. This text is displayed in the input box’s title bar.
  • Default: It is also optional. The default value showed in the input field.
  • Xpos: It is a numeric value that indicates the prompt’s horizontal distance from the left side of the screen (X-axis position); if left empty, the input box will be horizontally centered.
  • Ypos: It is an optional argument as well. It tells of the Y-axis positioning location of the InputBox.
  • Helpfile: to provide a user-friendly assistance file. By pressing the help button, the person can open that file.
  • Context: It indicates the HelpContextId in the ‘Helpfile’ that is being called. It’s an optional parameter. But it becomes required when the ‘Helpfile’ argument is given.

Introduction to Excel VBA Application.InputBox Method

You also can use the Application.InputBox method to do the same task. But it has some advantages over the InputBox function.

  • We can define the resulting datatype.
  • You can identify when someone clicks the Cancel.
  • You may quickly determine whether the return value is False by assigning it to a Variant.

Arguments of Application.InputBox Method

Objective:

The objective of this method is also the same as the previous function. It also returns an InputBox with some advanced features.

Syntax:

The syntax for this method is kinda same as the InputBox function. It’s as follows.

Application.InputBox(Prompt, [Title], [Default], [Left], [Top], [Helpfile], [HelpContextID], [Type])

Parameters:

The arguments of this method are mostly similar to the arguments of the InputBox function. There is just an extra parameter here which is Type. It’s an optional argument. Actually, it defines the type of returned data. In the absence of this option, the InputBox will just return text as a default. Here is the list of the value of the data types.

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

VBA InputBox for Integer Value Only in Excel: 8 Different Cases

Dataset of scores of exams

To move on to the next step, we will use the above dataset to perform the exercises. This is the Score of the Mid-Test of students of a particular institution. This dataset includes the Std ID, their Names, and corresponding Marks under columns B, C, and D respectively.

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

Now, we’ll utilize this dataset to create, visualize and use input boxes that take only an integer as input using VBA in Excel. So, let’s explore them one by one.

Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


1. Entering Marks of Students

In our very first example, we’ll show how we can input the marks of students using the input box in Excel. To achieve this goal, we’ll just take integer values as input and restrict any other data types in our input boxes. We can do this in two ways, to be precise, using two different methods. Let’s see these below.


1.1 Using InputBox Function to Input Marks of Students

msgbox showing warning message for entering invalid input

Here, we can see that a message box is showing a warning message that the input is invalid. It also tells us to enter any integer value in the corresponding cell. This happens whenever we tried to insert anything except the integer value in the input box.

VBA code for inputbox of integer value of students' marks

The following is the working code of this example. You can copy it and use it in your workbook too.

Sub inpt_box()
Dim userInput As Variant
Dim intValue As Integer
Dim i As Integer
For i = 5 To 14 ' Loop through the D5:D14 range
' Prompt user to enter an integer value
userInput = InputBox("Please enter an integer value for cell D" & i)
' Validate user input and convert to integer
If IsNumeric(userInput) And InStr(userInput, ".") = 0 And userInput > 0 Then
intValue = CInt(userInput)
Range("D" & i).Value = intValue ' Place the integer value in the cell
' Show a success message in a MsgBox
MsgBox "You entered the integer value: " & intValue & vbCrLf & "Value added to cell D" & i, vbInformation, "Success"
ElseIf userInput <> "" Then ' check if user input is not empty
' Show an error message in a MsgBox
MsgBox "Invalid input. Please enter an integer value for cell D" & i, vbCritical, "Error"
i = i - 1 ' Decrement i to repeat the loop for the same cell
Else ' If user input is empty, exit the loop
Exit For
End If
Next i
' If there are no empty cells and the loop has reached the end of the range, show an error message in a MsgBox
If i = 15 Then
MsgBox "Cannot add more values. Range D5:D14 is already full.", vbCritical, "Error"
End If
End Sub

Code Breakdown

The VBA macro written in this code will ask the user to enter integer values for the cells in the range D5:D14. This is accomplished by showing an input box for the user to enter an integer value in each cell of the range. Upon user input, the code verifies the value to make sure it is an integer value, devoid of decimal points and negative values. The InStr function in this code is used to determine whether the user input is a number or not. The InStr function searches for the occurrence of a character or substring within a string and returns the position of the first occurrence. If the input is correct, the value is added to the cell and a message box with a success message is displayed. An error notice is shown and the user is asked to submit a valid integer value if the input is invalid.

From the above video, we can see that at first, we tried to input a text string “er” in the input box and it rejected the input and returns a message to input an integer value. Again, we tried to enter a decimal number which results in the same. At last, it takes the input while we input an integer value. Then, it puts the value in cell D5 and again asked for another input for cell D6. This happens again and again until we enter the last value in cell D14.


1.2 Utilizing Application.InputBox Method to Insert Marks of Students

inputbox only taking integer value as input, otherwise shows invalid message

This example is the same as the previous one, just the difference is that here we used the Application.InputBox method to finish the task. This method has an advantage over the previous one. You can select the input data type with an argument in this method. Here, it’s visible that it shows a message box with the message “Number is not valid.” when we tried to enter a text string in the input box. Because we already restrict the input box from taking any other values except an integer number.

VBA code to input marks using Application.InputBox Method

Here is the code script of this method:

Sub App_inpt_box_method()
Dim userInput As Variant
Dim intValue As Integer
Dim i As Integer
For i = 5 To 14 ' Loop through cells D5 to D14
' Prompt user to enter an integer value for the current cell
userInput = Application.InputBox("Please enter an integer value for cell D" & i, Type:=1)
' Validate user input and convert to integer
If userInput <> False And userInput <> "" Then ' If the user clicked "Cancel", exit the loop
If IsNumeric(userInput) And InStr(userInput, ".") = 0 And userInput > 0 Then
intValue = CInt(userInput)
' Place the integer value in the cell
Range("D" & i).Value = intValue
' Do something with the integer value here
MsgBox "You entered the integer value: " & intValue
Else
' Show an error message in a MsgBox
MsgBox "Invalid input. Please enter an integer value for cell D" & i, vbCritical, "Error"
i = i - 1 ' Decrement i to repeat the loop for the same cell
End If
Else
Exit For ' Exit the loop if the user clicked "Cancel"
End If
Next i
End Sub

Code Breakdown

In this code, CInt is a function used to convert a value to an integer. Specifically, it is used to convert the user input, which is entered as a string, to an integer so that it can be assigned to a cell on the worksheet. The CInt function takes a single argument and returns the argument as an integer. If the argument cannot be converted to an integer, an error will occur.

Like Example 1.1, it also takes integer values as input and puts the value sequentially in the D5:D14 range. It also returns an error warning message whenever someone tries to input values rather than integers. Once the value is set in cell D14, no more input boxes appear after that.

Read More: Excel VBA InputBox with Number Only Criteria


2. Calculating Average Marks of Students Exceeding a Threshold Score

average of student's marks who got above 50 marks in exam, showing result with vba msgbox

Here, our goal is to calculate the average marks of students. But here’s the twist, only those who scored more than the threshold mark will be included in this calculation. So, in our example, we wanted to know the average marks of students who got above 50. In the input box, we simply put 50 and it returns the result through a message box.

VBA code to calculate average marks of students above a threshold marks

Behold, the code for this example:

Sub Cal_avg_marks()
'Declaring variables
Dim threshold As Variant
Dim thresholdInt As Integer
Dim sum As Double
Dim count As Integer
' Prompt user to enter a threshold score
Do
threshold = InputBox("Please enter a threshold score (whole number)")
If IsNumeric(threshold) And InStr(threshold, ".") = 0 Then
thresholdInt = CInt(threshold)  ' Convert the user input _
to integer if it is a whole number
Exit Do
ElseIf threshold = "" Then
Exit Sub
Else
MsgBox "Invalid input. Please enter a whole number." _
' Display an error message if user input is invalid
End If
Loop
' Calculate average marks exceeding the threshold score
For i = 5 To 14                           ' Loop through _
a range of cells containing student marks
If Range("D" & i).Value > thresholdInt Then  ' Check _
if the student's mark is above the threshold score
sum = sum + Range("D" & i).Value         ' Add the _
mark to the sum if it is above the threshold
count = count + 1                        ' Increment _
the count of marks above threshold by 1
End If
Next i
' Display result in a message box
If count > 0 Then
MsgBox "The average marks of students exceeding the threshold score of " _
& thresholdInt & " is " & Format(sum / count, "0.00")  ' Display the _
average marks above the threshold score
Else
MsgBox "No students scored above " & thresholdInt   ' Display a message _
if no students scored above the threshold
End If
End Sub

The user is asked to provide a threshold score in this code, and it is verified that it is a numeric value. The program then loops through cells D5 to D14, determining if each cell’s value is above the threshold score and, if so, adding that value to a sum and increasing the count. The code then computes the mean scores for the pupils who scored higher than the cutoff and displays the result in a MsgBox. If no students achieve the required score, a MsgBox error message is displayed. A MsgBox will also display an error message if the user input is incorrect.

We can see that if a decimal number is entered, a prompt is displayed requesting input of whole numbers only. It only brings results when we give input of an integer number.

Read More: Excel VBA: InputBox Type with Examples


3. Finding Nearest Value Based on Input Integer Value

finding nearest higher marks holder based on integer input in the inputbox

In this example, we input an integer number in the input box and it returns a message with the name of the nearest higher marks holder student. As we entered 55 in the input box, it tells us that this value is less than the marks of Jorge Ramirez who got 58. You can check in the dataset that it’s the closest higher marks based on the input marks.

VBA code to find nearest higher marks holder

Feast your eyes upon the code for this example:

Sub Find_Closest()
' Declare variables
'Dim userInput As Variant
Dim rng As Range, cell As Range
Dim closestStudent As String, closestMarks As Integer
Dim currentMarks As Integer, diff As Integer
Dim found As Boolean
On Error GoTo ErrorHandler
' Get user input
userInput = Application.InputBox("Enter an Integer number:", "Find closest value")
' Check if user input is an integer
If Not IsNumeric(userInput) Or userInput Mod 1 <> 0 Then
GoTo ErrorHandler
End If
' Loop through the range to find closest value
Set rng = Range("D5:D14")
For Each cell In rng
currentMarks = cell.Value
' Check if current marks are greater than user input
If currentMarks > userInput Then
' If no closest marks have been found yet, set current cell as closest
If Not found Then
found = True
closestMarks = currentMarks
closestStudent = cell.Offset(0, -1).Value
diff = Abs(currentMarks - userInput)
' If closest marks have been found, compare with current cell to find closer
Else
If Abs(currentMarks - userInput) < diff Then
closestMarks = currentMarks
closestStudent = cell.Offset(0, -1).Value
diff = Abs(currentMarks - userInput)
End If
End If
End If
Next cell
' Display result
If found Then
MsgBox "The value is less than the marks of " & closestStudent & "."
Else
MsgBox "No student has marks greater than the entered value."
End If
Exit Sub
ErrorHandler:
MsgBox "Invalid input. Please enter a whole number."
Exit Sub
End Sub

Code Breakdown

Abs is a function that gives back a number’s absolute value, which is the magnitude of the number without taking into account its sign. This code computes the difference between currentMarks and userInput without taking into account whether one is bigger or less than the other.

A range that is a specific number of rows and/or columns away from a given range is returned by the cell.Offset property. Cell.Offset(0, -1) is used in this code to retrieve the student name from the adjacent cell to the left. This range is 1 column to the left of the current cell. Ranges must be on the same row as cells, as indicated by the 0 in the Offset method.

At first, we tried to enter a text string. Instantly, the system rejects the input and tells us to enter a whole number. Then, after entering 90 in the input box, it returns the name of the closest higher marks holder name: Seth Rodriguez.

Read More: Excel VBA: InputBox with Default Value


4. Determining Factorial of an Integer

Calculating factorial of an integer input

We can easily determine the factorial of any integer number with this example. Here, the input box takes only an integer value as input and in return, it gives its factorial in a message box. It only takes integer input, otherwise, it’ll show a warning message of invalid input.

VBA code to calculate factorial of integer number

Cast your gaze upon the code for this example:

Sub Cal_Fact()
Dim n As Variant
Dim factorial As Double
n = Application.InputBox("Enter a positive integer (between 1 and 170):", "Factorial Calculator", Type:=1)
If IsNumeric(n) And Int(n) = n And n > 0 And n <= 170 Then
factorial = 1
For i = 1 To n
factorial = factorial * i
Next i
MsgBox "The factorial of " & n & " is " & factorial
Else
MsgBox "Invalid input. Please enter a positive integer between 1 and 170."
End If
End Sub

Code Breakdown

This code defines a subroutine called Cal_Fact() that calculates the factorial of a positive integer inputted by the user. Here’s a step-by-step breakdown of what the code does:

  • At first, we declared the variables to take input and store data into it. n will hold the user’s input, and factorial will hold the calculated factorial.
  • Secondly, we prompt the user to input a positive integer between 1 and 170 using an input box. The user’s input is then assigned to the n.
  • The If statement checks whether the user’s input is a positive integer between 1 and 170. IsNumeric(n) checks whether the input is a number, Int(n) = n checks whether it’s a whole number (i.e., has no decimal places), n > 0 checks whether it’s positive, and n <= 170 checks whether it’s within the given range.
  • If the user’s input passes the validation checks, the code calculates the factorial using a For loop.
  • After the nth iteration it shows the result in a MsgBox.
  • If the input is anything except an integer value, it returns a message box with an error message and tells us to input an integer between 1 to 170.

It didn’t show the factorial when we entered 6.5 as input. Because the input box is taking only integer input and factorial can be calculated for only non-negative integers. So, it shows the answer 720 when we enter 6 in the input dialog box.

Read More: Excel VBA: Custom Input Box 


5. Drawing Square of Given Size Using Asterisk

drawing a square in the immediate window using given integer size

In this example, we drew a square shape with the help of asterisk signs. In the input box, we have to simply enter the size of one side of the square and the code will do the rest. Immediately, it’ll show the square shape in the immediate window below the code. In the above picture, we input 5 in the input box, as a result, it created a square that has 5 asterisks on each side.

VBA code to draw square of given size

The code for this example is as follows:

Sub Draw_Square()
size = InputBox("Enter the size of the square:")
If IsNumeric(size) And InStr(size, ".") = 0 Then
S1 = ""
For i = 1 To size
S1 = S1 + "*" + " "
Next i
S1 = Left(S1, Len(S1) - 1)
Debug.Print (S1)
S2 = ""
For i = 1 To size - 2
S2 = S2 + "*"
For j = 1 To (size * 2) - 3
S2 = S2 + " "
Next j
S2 = S2 + "*"
Debug.Print (S2)
S2 = ""
Next i
Debug.Print (S1)
Else
MsgBox "Invalid input. Please enter a positive integer."
End If
End Sub

Code Breakdown

This code defines a subroutine called “Draw_Square” that draws a square made of asterisks (*) based on the user’s input.

  • First, an input box appears and asks the user to enter the size of the square. The input is stored in the “size” variable.
  • The code then checks whether the input is a numeric value and does not contain a decimal point using the “IsNumeric” and “InStr” functions. If the input is valid, the code proceeds to draw the square.
  • To draw the square, the code first initializes an empty string called “S1“. It then uses a “For” loop to add an asterisk followed by a space to S1 for “size” number of times. The loop stops when “i” is equal to “size“. After the loop, the last space is removed from S1 using the “Left” function, and the resulting string is printed to the Immediate Window using the “Print” statement.
  • The code then initializes another empty string called “S2“. It uses two nested “For” loops to create the middle rows of the square. The outer loop runs from 1 to “size-2” and the inner loop runs from 1 to “(size*2)-3“. The number of spaces is calculated as (size * 2) – 3. This is because the square has a “size” number of asterisks in each row, and there is one asterisk on either end of the row. The remaining spaces are filled with spaces.
  • After the inner loop, another asterisk is added to the end of “S2” and the resulting string is printed to the Immediate Window using the “Print” statement. The “S2” string is then reset to an empty string.
  • Once all the middle rows are printed, the code prints the top and bottom rows of the square by printing the “S1” string again.
  • If the user input is invalid, the code displays a message box telling the user to enter a positive integer.

Note: In this example, we opt to get the output in the Immediate Window, not on a message box. By default, it isn’t visible in Excel. We have to enable this feature. To do this, go to the View tab >> select Immediate Window.

Enabling immediate window

After executing the code, it prompts an input box to take the size of the square to be drawn. When we tried to enter a double number, it tells us to enter an integer number. As soon as we entered 5 in the box, it drew a square shape with 5 asterisks on each side in the Immediate Window.


6. Checking If an Integer Is Prime

showing error message as we select a string value in the VBA inputbox

In this case, we have to choose a number from the Marks column and Excel will say if it is a prime number or not. To give input, we’ll use an input box. But this box will take only integer values. As an example, we chose the value in cell C5 which is a text string. As a result, it returns that the input is invalid and tells us to make sure that the input value must be an integer number.

VBA code to check if an integer is Prime or not

Following is the working code:

Sub Prime_checker()
'Dim n As Integer
Dim i As Integer
Dim isPrime As Boolean
n = Application.InputBox("Select any integer value from marks", _
"Checking Prime Number", , , , , , 8)
If IsNumeric(n) And InStr(n, ".") = 0 And n > 1 Then
isPrime = True
For i = 2 To n - 1
If n Mod i = 0 Then
isPrime = False
Exit For
End If
Next i
If isPrime Then
MsgBox n & " is a prime number"
Else
MsgBox n & " is not a prime number"
End If
Else
MsgBox "Invalid input. Please enter a positive integer greater than 1."
End If
End Sub

The “Mod” operator is used in this code to determine whether or not a user-entered number is a prime number. By dividing one integer by another, the “Mod” operator returns the remaining amount.

This line determines whether the result of multiplying the user-inputted number (n) by the current iteration’s number (i) is zero. If it is, the isPrime variable is set to False, indicating that the number is not prime. The isPrime variable is set to True if the residual is not zero for any value of I between 2 and n-1. In this case, the number is prime.

Firstly, we select the value of cell C5 which is a text string. So, it returns an error message and remember us to input an integer value. Then, we selected cell D5 which has 45 as its value and it returns that 45 is not a prime number which is absolutely true. Again, we tried it for 11 and it’s a prime number which is also validated by our code.


7. Converting an Integer to Binary

converting integer value to binary number format

In this instance, our objective is to convert an integer number to a binary number format. To illustrate, we selected the marks of Ivan Hernandez in cell D8 in the prompted input box. As a result, it returned the binary value of this selected integer (40) in the message box.

VBA code to conert an integer to binary

The following snippet contains the code for this example:

Sub Convert_to_Binary()
'Declare variable binary as a string to store the binary value
Dim binary As String
'Prompt the user to input an integer to convert to binary
n = Application.InputBox("Select a integer from marks to convert into binary:", _
"Convert to Binary", Type:=8)
If IsNumeric(n) And InStr(n, ".") = 0 And n >= 0 Then 'Check if the input _
value is a positive integer
binary = "" 'Initialize binary as an empty string
Do While n > 0 'Start a loop to convert the integer to binary
binary = CStr(n Mod 2) & binary 'Take the remainder of n divided _
by 2 and concatenate it to binary
n = n \ 2 'Divide n by 2 and round down to the nearest integer
Loop
MsgBox "The binary value of selected integer is " & binary 'Display the _
binary value in a message box
Else 'If the input value is not a positive integer
MsgBox "Invalid input. Please enter a positive integer." 'Display an _
error message
End If
End Sub

When we execute the code, it asks us for selecting an integer from the marks on our dataset. After selecting cell D8 (which has the value of 40), it returns the binary format of this decimal number. But, whenever we selected cell C5 which has the value of text string, it didn’t accept that input, said it to be invalid, and told us to enter a positive integer number.


8. Generating Random Integer Within Specified Range

generating random number between two limits

With regard to this specific case, we intend to create a random number based on our inputs. Simply, we have to give two inputs: one for the minimum value, and another for the maximum value. This should be done by input boxes and those input boxes just accept integer values. Otherwise, it gives an error message. Here, we gave limits of 15 and 35 and Excel returns 21 as output.

VBA code to generate integer number within a specified range

Here’s what the code for this example looks like.

Sub Generate_Random_Number_Between_Min_Max()
'Declare variables as integer to store values
Dim min As Integer
Dim max As Integer
Dim random As Integer
'Prompt the user to input the minimum and maximum values
min = InputBox("Enter the minimum value:")
max = InputBox("Enter the maximum value:")
If max > min Then 'Check if the maximum value is _
greater than the minimum value
random = Int((max - min + 1) * Rnd + min) 'Generate a _
random integer between the minimum and maximum values _
using the Rnd function
MsgBox "The random number is " & random 'Display the _
random number in a message box
Else 'If the maximum value is not greater than the minimum value
MsgBox "Invalid input. Please enter a valid range." 'Display an _
error message
End If
End Sub

After executing the code, it asks for two inputs through two different input boxes. On successfully giving them, it returns an output of a random integer number in the message box. You can understand from the above video that every time it gives a different output for same inputs.


How to Create Multiple Prompt Lines Inside VBA InputBox in Excel

Creating inputbox with multiple prompt lines

Normally, we observe that the prompt line on an InputBox appears in a single line. But we can create multiple lines on the prompt of an InputBox through VBA code. Just see the following steps.

VBA code to build inputbox with multiple prompt lines

In the VBA module, just paste this code:

Sub Multiple_Lines()
InBx_MltLn = InputBox(Prompt:="PromptString1" _
& vbNewLine & vbNewLine & "PromptString2" _
& vbNewLine & vbNewLine & "PromptString3")
End Sub

From the video, you can notice that there are three lines of prompt visible on the InputBox. Generally, which doesn’t happen in Excel. Actually, there are five lines. Two are just blank lines between the three prompt lines. This technique can be helpful to give instructions to users sometimes.

Read More: Vbscript InputBox with Multiple Fields in Excel


How to Handle User Cancellation in Excel VBA InputBox

Detecting if user cancelled the inputbox

From the overview image, it’s evident that Excel returned a message box when a user canceled or close the InputBox. It can be done through user cancellation handling. An essential part of programming is handling user cancellation in Excel VBA InputBox since it can reduce unexpected behavior and enhance user satisfaction. You can make sure that your code operates as expected and is user-friendly by utilizing an If statement to check for cancellation.

VBA code to handle user cancellation

You can find the code for this example below:

Sub InputBox_Cancel()
Dim n As String
n = InputBox("Enter marks of students")
If StrPtr(n) = 0 Then
MsgBox ("User cancelled the InputBox!")
ElseIf n = vbNullString Then
MsgBox ("Blank input field!")
Else
MsgBox ("Marks entered is " & n)
End If
End Sub

From the above video, it’s clearly visible that when we are entering an integer value, a message box returns the result. If we keep the input box empty and click OK on it, it gives a different message. Also, when we click on the Cancel button or the Close icon of the input box, it returns another message. So, depending on the situation, it informs us about the user’s actions.

Read More: Excel VBA: InputBox Date Format


Frequently Asked Questions

  • What happens if the user enters a non-numeric value in the InputBox?

The IsNumeric function will return False, signifying that the input is not a valid numeric value, if the user inputs a non-numeric number in the InputBox. The user may then be prompted to provide a correct integer value or an error notice may be displayed.

  • What is the maximum and minimum value of an integer in VBA?

In VBA, an integer can have a maximum value of 32,767 and a minimum value of -32,768. The Long data type, which has a maximum value of 2,147,483,647 and a minimum value of -2,147,483,648 might be used if you need to work with greater values.

  • Can I customize the appearance of the InputBox window in VBA?

Using the optional arguments of the InputBox function, you can change the title, default value, and position of the window to alter how the InputBox window looks.

  • Can I use the InputBox function to receive values other than integers in VBA?

Absolutely, depending on the needs of your code, the InputBox function can be used to receive values of various data kinds, such as strings or dates. To store the value obtained from the InputBox, the proper data type must be used. Use Application.InputBox method to easily set the input data type.


Things to Remember

  • Don’t declare the variable of the InputBox as an integer at the start of the code. You can omit to declare this variable. Otherwise, declare it as a Variant type variable.
  • Make sure to validate the user’s input that it’s a valid integer. You can accomplish this by first determining whether the input is a numeric value using the IsNumeric function, and then by converting the input to an integer value using the CInt.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

In conclusion, the user can now call VBA InputBox for integer value only in Excel. We can get an integer value from the user and deal with any potential issues by utilizing the InputBox function and some recommended practices.

We showed some real-life examples of using the Inputbox function to take integer values only as input. By this reference, we can learn to calculate the average marks of students, determine the factorial of integer numbers, and so on.

Also, we discussed creating multiple prompt lines, and user cancellation handling like topics. Don’t forget to go through the last two sections Things to Remember and FAQs for some important insights which we faced while working on this topic. That’s all from us.

Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

What is ExcelDemy?

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

Tags:

Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo