Validating with Like

[Karen53]

Hi,

I've been trying to validate the value of a textbox with partial success.
The value input must be a number but it can also be a decimal number. I am
trying to catch alpha characters but my Like statement is not catching it. I
have tried various versions, "A-Z", "a' - "z", a-z.

Sub EditAllowedVariance(PasswordGood)

Dim NuAllowance As String

If PasswordGood = True Then
NuAllowance = InputBox("Enter desired allowed variance", _

"Enter Variance")
If NuAllowance Like ["a-z"] Then
MsgBox "Variance must be a number"
Goto InvalidNo
End If
If Val(NuAllowance) < 0 Then
MsgBox "Allowance must be positive number"
GoTo InvalidNo
Else
GrossUppg.Range("I2").NumberFormat = "00.00"
GrossUppg.Range("I2").Value = Val(NuAllowance)
End If
End If

InvalidNo:

End Sub

 

[RB Smissaert]

You may find this function useful, which I use to remove non-numeric
characters from a textbox as they are typed:

Function RemoveNonNumeric2(strString As String, _
Optional bIntegerOnly As Boolean, _
Optional bPositiveOnly As Boolean, _
Optional bClearAndOut As Boolean, _
Optional bHasChanged As Boolean) As String

Dim i As Long
Dim c As Long
Dim bHasDot As Boolean
Dim btArray() As Byte

'45 -
'46 .
'48 to 58 0 to 9
'------------------

btArray = strString

If bIntegerOnly Then
If bPositiveOnly Then
For i = 0 To UBound(btArray) Step 2
If btArray(i) < 48 Or btArray(i) > 58 Then
bHasChanged = True
If bClearAndOut Then
RemoveNonNumeric2 = vbNullString
Exit Function
End If
strString = Left$(strString, i \ 2 - c) & _
Mid$(strString, i \ 2 + 2 - c)
c = c + 1
End If
Next i
Else 'If bPositiveOnly
For i = 0 To UBound(btArray) Step 2
If btArray(i) < 48 Or btArray(i) > 58 Then
If i - c * 2 = 0 Then
If btArray(i) <> 45 Then
bHasChanged = True
If bClearAndOut Then
RemoveNonNumeric2 = vbNullString
Exit Function
End If
strString = Left$(strString, i \ 2 - c) & _
Mid$(strString, i \ 2 + 2 - c)
c = c + 1
End If
Else 'If i - c * 2 = 0
bHasChanged = True
If bClearAndOut Then
RemoveNonNumeric2 = ""
Exit Function
End If
strString = Left$(strString, i \ 2 - c) & _
Mid$(strString, i \ 2 + 2 - c)
c = c + 1
End If 'If i - c * 2 = 0
End If
Next i
End If 'If bPositiveOnly
Else 'If bIntegerOnly
If bPositiveOnly Then
For i = 0 To UBound(btArray) Step 2
If btArray(i) < 48 Or btArray(i) > 58 Then
If btArray(i) <> 46 Or bHasDot Then
bHasChanged = True
If bClearAndOut Then
RemoveNonNumeric2 = vbNullString
Exit Function
End If
strString = Left$(strString, i \ 2 - c) & _
Mid$(strString, i \ 2 + 2 - c)
c = c + 1
Else
bHasDot = True
End If
End If
Next i
Else 'If bPositiveOnly
For i = 0 To UBound(btArray) Step 2
If i - c * 2 = 0 Then
If btArray(i) < 48 Or btArray(i) > 58 Then
If btArray(i) <> 45 Then
If btArray(i) <> 46 Or bHasDot Then
bHasChanged = True
If bClearAndOut Then
RemoveNonNumeric2 = vbNullString
Exit Function
End If
strString = Left$(strString, i \ 2 - c) & _
Mid$(strString, i \ 2 + 2 - c)
c = c + 1
Else
bHasDot = True
End If
End If
End If
Else 'If i - c * 2 = 0
If btArray(i) < 48 Or btArray(i) > 58 Then
If btArray(i) <> 46 Or bHasDot Then
bHasChanged = True
If bClearAndOut Then
RemoveNonNumeric2 = vbNullString
Exit Function
End If
strString = Left$(strString, i \ 2 - c) & _
Mid$(strString, i \ 2 + 2 - c)
c = c + 1
Else
bHasDot = True
End If
End If
End If 'If i - c * 2 = 0
Next i
End If 'If bPositiveOnly
End If 'If bIntegerOnly

RemoveNonNumeric2 = strString

End Function


RBS

Hi,

I've been trying to validate the value of a textbox with partial success.
The value input must be a number but it can also be a decimal number. I
am
trying to catch alpha characters but my Like statement is not catching it.
I
have tried various versions, "A-Z", "a' - "z", a-z.

Sub EditAllowedVariance(PasswordGood)

Dim NuAllowance As String

If PasswordGood = True Then
NuAllowance = InputBox("Enter desired allowed variance", _

"Enter Variance")
If NuAllowance Like ["a-z"] Then
MsgBox "Variance must be a number"
Goto InvalidNo
End If
If Val(NuAllowance) < 0 Then
MsgBox "Allowance must be positive number"
GoTo InvalidNo
Else
GrossUppg.Range("I2").NumberFormat = "00.00"
GrossUppg.Range("I2").Value = Val(NuAllowance)
End If
End If

InvalidNo:

End Sub

 

[Rick Rothstein \(MVP - VB\)]

Here are two functions that I have posted in the past (over in the compiled
VB newsgroups, but they work fine in VBA)... one is for digits only and the
other is for "regular" numbers:

Function IsDigitsOnly(Value As String) As Boolean
IsDigitsOnly = Len(Value) > 0 And _
Not Value Like "*[!0-9]*"
End Function

Function IsNumber(ByVal Value As String) As Boolean
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) > 0 And Value <> "." And _
Value <> vbNullString
End Function

Here are revisions to the above functions that deal with the local settings
for decimal points (and thousand's separators) that are different than used
in the US (this code works in the US too, of course).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

I'm not as concerned by the rejection of entries that include one or more
thousand's separators, but we can handle this if we don't insist on the
thousand's separator being located in the correct positions (in other words,
we'll allow the user to include them for their own purposes... we'll just
tolerate their presence).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
Dim TS As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Get local setting for thousand's separator
' and eliminate them. Remove the next two lines
' if you don't want your users being able to
' type in the thousands separator at all.
TS = Mid$(Format$(1000, "#,###"), 2, 1)
Value = Replace$(Value, TS, "")
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

Rick

Hi,

I've been trying to validate the value of a textbox with partial success.
The value input must be a number but it can also be a decimal number. I
am
trying to catch alpha characters but my Like statement is not catching it.
I
have tried various versions, "A-Z", "a' - "z", a-z.

Sub EditAllowedVariance(PasswordGood)

Dim NuAllowance As String

If PasswordGood = True Then
NuAllowance = InputBox("Enter desired allowed variance", _

"Enter Variance")
If NuAllowance Like ["a-z"] Then
MsgBox "Variance must be a number"
Goto InvalidNo
End If
If Val(NuAllowance) < 0 Then
MsgBox "Allowance must be positive number"
GoTo InvalidNo
Else
GrossUppg.Range("I2").NumberFormat = "00.00"
GrossUppg.Range("I2").Value = Val(NuAllowance)
End If
End If

InvalidNo:

End Sub

 

[Karen53]

Thank you, both!