Thread: Numeric validation on userform not working, could be a formating issue

Hi Guys

I’m developing a userform in Excel for a client, Some of the data they’ll be entering will be pricing information, including a ‘now price’ and a ‘was price’. The client has asked me to add some validation to the form warning the user if they enter a now price that is greater than the was price. I thought I’d got it working fine as I tested it and everything seem to work. However a problem has become apparent.

Say if I was to enter a now price of 9.99 and a was price of 19.99 the warning would come up saying that the was price is higher than the now price. I’ve isolated the problem to the number of digits used. If I was to change the now price to 10.99 the warning wouldn’t appear as it has 2 figure before the decimal. I’m sure this would have something to do with my formatting and the userform not seeing the data entered as numbers but I’m at a loss on how to format these fields as numbers or currency.

This is my code

'check pricing (now1/was1)
If Len(Me.was1.Value) = 0 Then
Me.was1.Value = ""
ElseIf (Me.now1.Value) > (Me.was1.Value) Then
  Me.now1.SetFocus
  MsgBox "Your 'Now Price' is greater or the same as your 'Was Price'"
  Exit Sub
End If

The LEN bit at the beginning allows the user to omit entering a value without getting a warning (I don’t know if there’s a better way to do this bit, suggestions welcome) and the rest of the code does the validation.

If any of you good people out there could point out what I’m doing wrong I would be moost greatful.

Thanks in advance.

Thom

Hi,

It's because the > sign is performing a string comparison (text typed into textboxes are strings).

Convert the strings to double data types first, and only then perform the comparison.

Hope that helps...

There are two functions available in VBA to do this: CDbl() and Val().

Here is a simple example with CDbl():

Sub example()
    Const strValue1 As String = "9.99"
    Const strValue2 As String = "19.99"
    
    Dim dblValue1 As Double
    Dim dblValue2 As Double
    
    dblValue1 = CDbl(strValue1)
    dblValue2 = CDbl(strValue2)
    
    MsgBox strValue1 > strValue2
    MsgBox dblValue1 > dblValue2
    
End Sub

You'll need to consider some defensive coding/error handling: if the string in the textbox cannot be converted to a double then CDbl() would raise a Type Mismatch runtime error.