Validating numeric values in Userform textbox|VBA & Macros|Excel Forum|My Online Training Hub

Hi, - New here.

I'm baffled by an issue on a userform. I have two textbox's One called "Available1" and one called "Add1". "Available1" gets it's value from a cell on the sheet when the form fires up - Usually a number from 1 to a couple of hundred e.g. 250. I have the following code on the Change event of "Add1" to test for...

a) That a number is entered (i.e. Not text)

b) That the number is greater than 0

c) That the number is not higher than the value in "Available1"

d) That the number is a whole number (no decimal points)

Private Sub Add1_Change()

If Not IsNumeric(Me.Add1.Text) And Add1.Text <> "" Then

If Len(Add1.Text) > 1 Then

Add1.Text = Abs(Round(Left(Add1.Text, Len(Add1.Text) - 1), 0))

Else

Add1.Text = ""

End If

ElseIf Add1.Text <> "" Then

If Add1.Text = 0 Then

Add1.Text = ""

ElseIf Add1.Text > Available1.Text Then

If MsgBox "Are you sure you want to enter a number higher than the amount available?", vbYesNo + vbCritical) = vbNo Then

Add1.Text = ""

Else

Add1.Text = Abs(Round(Add1.Text, 0))

End If

End If

End Sub

All works fine apart from the greater than element. It seems to only recognise the first digit when applying the code. e.g. If "Available1" has a single digit number 1 - 8 if I entered 9 it would popup the message. However, if I entered 10 it would ignore the fact that its higher. It's as if it just recognises the first digit from each textbox. I have limited experience with VBA and am baffled. I'd be grateful for some assistance.