When is a number not a number?

[ACommandLineKindaGuy]

I have the following code to check the input of a Textbox.

So, for say TextBox301, I call a routine to validate the number I enter. I have a lot of groups of TextBoxes and they all have different maximum integer values, so I pass the maximum value using public constant n:

<code>Private Sub TextBox301_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Call ValidateInput(TextBox301, 5)

End Sub

Private Sub ValidateInput(MyControl, n) 'n = Control's max value

'#UNDER DEVELOPMENT############### THE PROBLEM IS THE .Value > n STATEMENT
'We only have to check for integers since the maximum characters is 1...
With MyControl
If Not IsNumeric(.Value) Or .Value < 1 Or .Value > n Then
MsgBox "Must be numeric, or an integer between 1 and " & n & "... ", vbCritical, "Invalid Entry:"
.SelStart = 0
.SelLength = .MaxLength + 1
Cancel = True
End If
End With
'#END UNDER DEVELOPMENT###########
End Sub</code>

The problem is with the ".Value > n" portion of the If statement. If I remove it, the If statement doesn't fire. If I substitute ".Value * 1 > n" it also doesn't fire, but then of course the routine errors out when I enter a letter.

So, anyone have any idea WHAT is going on? If I had any hair, I'd be bald...

 

[Rick Rothstein]

'We only have to check for integers since the maximum characters is 1...
With MyControl
If Not IsNumeric(.Value) Or .Value < 1 Or .Value > n Then

If the value of 'n' is always a single digit number, you can use this If..Then test for it...

If .Value Like "[1-" & n & "]" Then

 

[ACommandLineKindaGuy]

Thanks--works great. But, I still don't understand why we have to use this workaround...

 

[Rick Rothstein]

Thanks--works great. But, I still don't understand why we have to use this workaround...

This was your line of code...

If Not IsNumeric(.Value) Or .Value < 1 Or .Value > n Then

I think a problem occurs when .Value is not a number... I believe the test for being less than or greater than a number will fail and raise a "Type mismatch" error. If you wanted to stick with your logic, I think you would need to do your tests something like this...

If Not IsNumeric(.Value) Then
  ' Display your error warning here
ElseIf .Value < 1 Or .Value > n Then
  ' Display your error warning here
Else
  ' Everything is okay so put your continuing code here
End If

 

[ACommandLineKindaGuy]

Rory,

LOL, well that makes sense: a TextBox contains text

Rick,

Given Rory's astute observation, seems that an easier way for be to simply use VAL(.Value)...

Thanks again guys

 

[RoryA]

FYI, I removed my earlier post as my comment about > not interpreting numbers was not correct.

Val is fine as long as you don't have international users to worry about, or use any currency formatting in the textboxes.

 

[Rick Rothstein]

Rick,

Given Rory's astute observation, seems that an easier way for be to simply use VAL(.Value)...

Yes, as long as your TextBox is set to receive a maximum of one character. I would note, however, that you will need to do two comparisons (And'ed together), one to make sure the value is not 0 and the other to make sure it is less than or equal to n whereas my suggestion only requires the single Like comparison.