for i = questions

[Ed Greenbank]

I would like to be able to run validation on a field to make sure it
contains at least so many space characters.

For instance, a "Full Name" field would require at least one space . Eg. "Ed
Greenbank"
(and anything from 3 to 30 characters in length would be valid)

Also, is it possible to validate a field so that it must contain at least 25
characters, at least 3 spaces, and a maximum of 10 of these can be numeric
characters (or no more than 50% of the string of text can be numeric
characters)?

Last question...
How do you refer to the maximum number of characters? Say if there must be
at least 6 characters entered (but no limit) and I want all of the
characters to be numeric.. how would i say For i = 1 to Infinity (etc.)?

Any light shed on any of these matters I would be grateful of.

Regards

 

[Jean-Guy Marcil]

Ed Greenbank was telling us:
Ed Greenbank nous racontait que :

I would like to be able to run validation on a field to make sure it
contains at least so many space characters.

What type of field are you writing about?
What Word version?

For instance, a "Full Name" field would require at least one space .
Eg. "Ed Greenbank"
(and anything from 3 to 30 characters in length would be valid)

Also, is it possible to validate a field so that it must contain at
least 25 characters, at least 3 spaces, and a maximum of 10 of these
can be numeric characters (or no more than 50% of the string of text
can be numeric characters)?

Yes, but if you have too many conditions, it won't be simple...
Look up the VBA help on "LIKE"
Why don't you tell us what you are trying to achieve, there might be an
easier approach.

Last question...
How do you refer to the maximum number of characters? Say if there
must be at least 6 characters entered (but no limit) and I want all
of the characters to be numeric.. how would i say For i = 1 to
Infinity (etc.)?

Look up the VBA help on "LEN"

 

[Pesach Shelnitz]

Hi Ed,

Jean-Guy gave you some good initial advice, but I'll try to give you
examples for your specific questions, because, in my opinion, validating user
input should be
a general practice.

In addition to the Len function and the LIKE operator, you should become
familiar with the InStr function, which lets you check for the presence of
any string or
individual character anywhere in your test string, as well as the Mid
function, which lets you extract a string or an individual character from
your test string for further testing.

The first of the following macros checks that the test string contains at
least one space and 3 to 30 characters, and the second macro checks that the
test string contains at least 25 characters, of which at least 3 are spaces
and no more than 10 are numerical characters.

Sub ValidateSpaceAndLength()

Dim fieldText As String
Dim x As Long

fieldText = "Ed Greenbank"
x = InStr(1, fieldText, " ", vbTextCompare)
If x = 0 Or Len(fieldText) < 3 Or Len(fieldText) > 30 Then
MsgBox "The field text does not contain any spaces " _
& "or does not contain 3 to 30 characters."
Else
MsgBox "The field text contains at least one space " _
& "and the required number of characters."
End If
End Sub

Sub ValidateLengthSpaceAndNumeric()
Dim fieldText As String
Dim myChar As String
Dim i As Long
Dim numInt As Integer
Dim numSpace As Integer

fieldText = "The password is 0A1B2C3D4E5F6G7H8I9"
For i = 1 To Len(fieldText)
myChar = Mid(fieldText, i, 1)
If myChar = ChrW(32) Then
numSpace = numSpace + 1
End If
If IsNumeric(myChar) = True Then
numInt = numInt + 1
End If
Next
If Len(fieldText) < 25 Or numSpace < 3 Or numInt > 10 Then
MsgBox "The field text contains less than 25 characters, " _
& "or does not contain 3 spaces, or " _
& "or contains more than 10 numerical characters."
Else
MsgBox "The field text contains at least 25 characters, " _
& "of which at least 3 are spaces and " _
& "no more than 10 are numerical characters."
End If
End Sub

I suggest that you play around with these macros by changing the string in
the fieldText variable so that it will fail the tests.

The possibilities for validation code are virtually endless, and I hope that
these examples will put you on the path to writing your own validation code
for any type of user input.

 

[Ed Greenbank]

Many thanks, especially Pesach for taking the time to write the code.