Validate Text in Textbox to Ensure Text is a Valid Price Format

[Ryan H]

I have several textboxes on several userforms that I want to test the
contents of the textboxes to ensure a properly formatted price is entered
like this "#,###.##" or "####.##". This is the Sub that I use for that and
it works great. My question is, is there a easier way or cleaner coded way
to do this?

Sub ValidatePrice(strPrice As String, strControlName As String)
' is called to ensure proper format of a price entered in a textbox

Dim strChars As String

' format price first
strPrice = Format(strPrice, "#,###.00")

' remove unwanted characters (myChars) from price
strChars = "$, "
For i = 1 To Len(strChars)
strPrice = Replace(strPrice, Mid(strChars, i, 1), "")
Next i

' ensure first number is 1-9
If Not Left(strPrice, 1) Like "[1-9]" Then
GoTo InvalidPrice
End If

' "." should be in the thrid position ####.##
If Mid(strPrice, Len(strPrice) - 2, 1) <> "." Then
GoTo InvalidPrice
End If

' scan each positon in Price string except position where period
' to ensure position value is numeric
For i = Len(strPrice) To 1 Step -1
If i <> Len(strPrice) - 2 And Not Mid(strPrice, i, 1) Like "[0-9]"
Then
GoTo InvalidPrice
End If
Next i

Exit Sub

InvalidPrice:

StopCode = True
strPrompt = "Problem"
intButtons = vbCritical
strTitle = "Please enter a valid price for " & strControlName & " in
this format: #,###.##"
MsgBox strTitle, intButtons, strPrompt

End Sub

 

[Rick Rothstein]

This is untested, but I believe it will work correctly and is how I would
structure the code to do what your code does...

' Called to ensure proper format of a price entered in a textbox
Sub ValidatePrice(strPrice As String, strControlName As String)
Dim X As Long, strChars As String

' remove unwanted characters (myChars) from price
strChars = "$, "
For X = 1 To Len(strChars)
strPrice = Replace(Mid(strPrice, X, 1), "")
Next

' format strPrice
strPrice = Format(strPrice, "0.00")

' display error if strPrice is not a valid number
If Value Like "*[!0-9.]*" Or Value Like "*.*.*" Or Len(Value) = 0 _
Or Value = "." Or Not Value Like "*.##" Then
strPrompt = "Problem"
intButtons = vbCritical
strTitle = "Please enter a valid price for " & strControlName & _
" in this format: #,###.##"
MsgBox strTitle, intButtons, strPrompt

' optional section... strPrice is correctly entered
Else
'
' use this section to give strPrice a final format, perhaps this?
'
' strPrice = Format(strPrice, "$#,##0.00")
'
End If
End Sub