Data Validation: Limit cell entry to [A-Z] [a-z]

[shawthingz]

Hi All,

I'm trying to solve a frustating problem - hope someone can help out there!

I'm currently using custom validation in various cells in a worksheet using the istext() function to restrict the characters a User can enter.

However, I've now been asked to restrict all characters entered to [A-Z] [a-z] only & I'm currently struggling to come up with a solution.

Some examples that would currently be accepted are:

shawth1ngz
shawthingz1
sh@wthingz


However I've now been asked to change the logic to fail them, so that only shawthingz (containing alphabetic chars) would be accepted, as the input eventually ends up being uploaded to a database (& various characters in some of the fields have been causing some problems).

Does anyone out there have any ideas on how to do this using cell validation?

Thanks for any help / guidance

shawthingz

 

[mikerickson]

pgc1, Nice

 

[shg]

pgc1, Nice

Indeed!

 

[pgc01]

Thanks! I got the idea from Mike's formula and I also like it a lot.

 

[shawthingz]

Hi All,

Apologies for my 'radio silence' over the past weeks, I was diverted onto various other higher priority things...

Many, many thanks for all the constructive comments & feedback on this question!

I didn't realise that this would be such a complex problem to resolve using data validation. I've also (recently) discovered that validation appears to 'break' from time to time between 2010 & previous versions of Excel

So, as I'm using a specific range on a specific worksheet (& the issue I mentioned above), I've abandoned Data Validation & implemented the following VBA within the "Sheet1" object (which seems to work):



Private Sub Worksheet_Change(ByVal Target As Range)


CellsChanged = Target.Address

If (Intersect(Range(CellsChanged), Range("$D$7:$e$106")) Is Nothing _
Or InStr(CellsChanged, ":") _
Or InStr(CellsChanged, ",")) Then

'Do Nothing - multiple cells have been changed!

Else

ValidCharString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
TotalNumberOfCharsToCheck = Len(Range(CellsChanged).Value)
TotalNumberOfValidChars = Len(ValidCharString)
CharNoInValidationString = 0
CharToCheckIfValid = 0
ValidChar = False
CurrentCharInString = ""
CurrentCharInValidationString = ""
ValidString = True

'Check all characters in the string entered for validity
For CharToCheckIfValid = 1 To TotalNumberOfCharsToCheck

'Set the counter back to the start of the validation string
CharNoInValidationString = 0

'Get the character we want to evluate
CurrentCharInString = Mid(Range(CellsChanged).Value, CharToCheckIfValid, 1)

'Set ValidChar back to False until it's found & validated
ValidChar = False

'Walk through the permitted values for this character in the string entered
For CharNoInValidationString = 1 To TotalNumberOfValidChars

CurrentCharInValidationString = Mid(ValidCharString, CharNoInValidationString, 1)

'Set the return value to True if the Char is not found
If (CurrentCharInString = CurrentCharInValidationString) Then
ValidChar = True
End If

Next CharNoInValidationString

Next CharToCheckIfValid

If ValidString = False Then
answer = MsgBox("PLEASE NOTE:" & Chr(13) & "Only [A-Z] and [a-z] can be entered into this field." & Chr(13) & _
"Please re-enter the User Name using only these characters." & Chr(13) & Chr(13) & _
"(The field will be automatically cleared when this box closes).", vbOKOnly + vbExclamation, "INVALID DATA ENTRY!")

CellsChanged = CellsChanged
Range(CellsChanged) = ""

End If

End If


Hoping this code isn't too rubbish (any additional hints / tips appreciated!)

shawthingz

 

[Biz]

I know I am late but try this method requires no names ranges.

Function Letters(ByVal Txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "[^A-Z\s]+"
    .Global = True
    .IgnoreCase = True
    Letters = Application.Trim(.Replace(Txt, ""))
End With
End Function

Biz

 

[shawthingz]

This looks rather more elegant than my "clunky" attempt...

Before I try & implement this, would this work for [A-Z][a-z] as these are the specific chars I need to support?

Thx

shawthingz

 

[Peter_SSs]

I've ... implemented the following VBA within the "Sheet1" object (which seems to work):



Private Sub Worksheet_Change(ByVal Target As Range)


CellsChanged = Target.Address

If (Intersect(Range(CellsChanged), Range("$D$7:$e$106")) Is Nothing _
Or InStr(CellsChanged, ":") _
Or InStr(CellsChanged, ",")) Then

'Do Nothing - multiple cells have been changed!

Else

ValidCharString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
TotalNumberOfCharsToCheck = Len(Range(CellsChanged).Value)
TotalNumberOfValidChars = Len(ValidCharString)
CharNoInValidationString = 0
CharToCheckIfValid = 0
ValidChar = False
CurrentCharInString = ""
CurrentCharInValidationString = ""
ValidString = True
'Check all characters in the string entered for validity
For CharToCheckIfValid = 1 To TotalNumberOfCharsToCheck

'Set the counter back to the start of the validation string
CharNoInValidationString = 0

'Get the character we want to evluate
CurrentCharInString = Mid(Range(CellsChanged).Value, CharToCheckIfValid, 1)

'Set ValidChar back to False until it's found & validated
ValidChar = False

'Walk through the permitted values for this character in the string entered
For CharNoInValidationString = 1 To TotalNumberOfValidChars

CurrentCharInValidationString = Mid(ValidCharString, CharNoInValidationString, 1)

'Set the return value to True if the Char is not found
If (CurrentCharInString = CurrentCharInValidationString) Then
ValidChar = True
End If

Next CharNoInValidationString

Next CharToCheckIfValid

If ValidString = False Then
answer = MsgBox("PLEASE NOTE:" & Chr(13) & "Only [A-Z] and [a-z] can be entered into this field." & Chr(13) & _
"Please re-enter the User Name using only these characters." & Chr(13) & Chr(13) & _
"(The field will be automatically cleared when this box closes).", vbOKOnly + vbExclamation, "INVALID DATA ENTRY!")

CellsChanged = CellsChanged
Range(CellsChanged) = ""

End If

End If


Hoping this code isn't too rubbish (any additional hints / tips appreciated!)

shawthingz

I'm afraid that I can't see how that could possibly work.

The message box indicating an invalid entry only appears if ValidString = False. However, the only other place in the code where ValidString is mentioned is setting it to True near the start of the code. How can it ever get to be False?

This would be my suggestion (slightly modified version of a suggestion I made at post #30 in this thread yesterday). It will also cope with multiple cell entries at once (eg via a Paste into the relevant range).

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rChanged <SPAN style="color:#00007F">As</SPAN> Range, rC <SPAN style="color:#00007F">As</SPAN> Range, rErrors <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sInvalid <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myInput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "D7:E106" <SPAN style="color:#007F00">'<- Input range to check</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> SeedCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1" <SPAN style="color:#007F00">'< -- any cell not in myIput</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rChanged = Intersect(Target, Range(my<SPAN style="color:#00007F">In</SPAN>put))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rChanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rErrors = Range(SeedCell)<br>        <SPAN style="color:#00007F">With</SPAN> CreateObject("VBScript.RegExp")<br>            .Pattern = "[a-zA-Z]"<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rC In rChanged<br>                s = rC.Value<br>                <SPAN style="color:#00007F">If</SPAN> .Replace(s, "") <> "" <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">Set</SPAN> rErrors = Union(rErrors, rC)<br>                    sInvalid = sInvalid & vbLf & rC.Address(0, 0) & vbTab & s<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> rC<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> sInvalid <> "" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rErrors = Intersect(rErrors, rChanged)<br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> rErrors<br>                .ClearContents<br>                .Select<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>            MsgBox "Invalid entries cleared from selected cell(s)" & _<br>                vbLf & "Cell" & vbTab & "Invalid entry" & sInvalid<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Peter_SSs]

BTW, another possible Data Validation formula approach (for cell D7)

=MAX(ABS(77.5-CODE(MID(UPPER(D7),ROW(INDIRECT("1:"&LEN(D7))),1))))<13

 

[shawthingz]

Thanks all,

Guess my code isn't quite as robust as I thought , so I'll investigate the many other solutions posted.

As ever, many thanks for the huge amount of positive collaboration!

shawthingz