Validating a cell contains an integer

I have written some code to validate that a user input field contains a whole number;

If VarType(Range("Quantity1")) = vbInteger Then

Else: MsgBox "Quantity must be a whole number, to rectify this please enter a whole number in the quantity field.", vbCritical, "Operator Response Required"
Range("Quantity1").ClearContents
Range("Quantity1").Select
Exit Sub
End If


This code returns an error message regardless of Quantity1 having an integer or not.
Can anyone help with this?

Quantity1 is the name of the cell to be validated.

Try vbdouble(not tested)

Please Login or Register  to view this content.

That's different issue.

Term Integer and vbInteger of VarType are different.

see

Please Login or Register  to view this content.

Originally Posted by cultofcargo

I have written some code to validate that a user input field contains a whole number;

Perhaps, OP wants to do like

Please Login or Register  to view this content.

Originally Posted by skywriter

Is "Quantity1" a cell on a spreadsheet?
VarType is supposed to be used with a variable.
You would have to create a variable, let's say x.
x = Range("Quantity1").Value
If VarType(x)......

Yes Quantity1 is a cell

x = Range("Quantity1").Value
If VarType(x) = vbInteger Then
Else
MsgBox "Quantity must be a whole number, to rectify this please enter a whole number in the quantity field.", vbCritical, "Operator Response Required"
Range("Quantity1").ClearContents
Range("Quantity1").Select
Exit Sub
End If


With this written i am still getting the error message no matter what is in the cell.

And storing x (quantity1) using Dim will return messages based on what i store it as in the Dim statement (string, boolean, integer)

Originally Posted by shg

All numbers in Excel are stored as Doubles. If you want to test that one is a whole number, then

Please Login or Register  to view this content.

Right: this isn't rocket science. Minor typo, corrected in red above (missing parenthesis). [1]

We might also point out that VBA Mod (jindon's Mod 1 expression) does not work for this purpose. As the help page tells us: VBA Mod works only with integer values. "If either number is a floating-point number, it is first rounded to an integer".


-----
[1] Personally, I would use the following; presumably more efficient.

Please Login or Register  to view this content.

Duplicate.