ExcelBanter - Disallow Continuous String of same Character

John,

"John" wrote in message
...
I want users to enter an explanation of cost variances if they exist in a
Note box. Currently I force this by checking the length of text input into
this box (say B10) if the cost variance (say in A1) exceeds a certain
amount. But is it possible to disallow from input if the text is a
continuous string of the same character eg xxxxxx or AAAAAA. Some employees
have worked it out that the Text box only requires a minimum of 10
characters in length so they just enter xxxxx (10 times) etc

Yes, you could do this, but you'd only be controlling one symptom of the
underlying problem - the users are not entering relevant data, and you want
them to. If a user figures out that he/she can't enter "AAAAAAAAAA", the
next try will probably be 0123456789. <g

If you have a finite list of cost variance explanations, it may be best to
store that list in a hidden worksheet, then use a dropdown or data
validation to force the user to select one of the explanations for each line
that has a cost variance.

That said, here's how you could check for a string of length =10 that
repeats the same character:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim sChar As String

With TextBox1
If Len(.Text) < 10 Then
MsgBox "Invalid entry."
Cancel = True
Else
sChar = Left$(.Text, 1)
If StrComp(.Text, String(Len(.Text), sChar)) = 0 Then
MsgBox "Nice try."
Cancel = True
End If
End If
End With
End Sub

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]