cell entry restricted to text, 2-3 letters & uppercase

Originally Posted by whitSC

I have been struggling to write a macro to automatically do the following in an Excel column range:
- limit the cell entry to text only (no periods, dashes, spaces, numbers, etc)
- restrict the text entry to either 2 or 3 letters
- convert the text entry to uppercase only.

Use Data Validation:
1. Select Column A.
2. Data --> Data Validation --> Settings --> Custom
3. Enter the following formula in the Formula box:
=ISTEXT(A1)
4. Click OK
http://www.exceltip.com/st/Restricti..._Only/884.html
2 to ensure not more then 2 characters in column A and change text to upper case

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If Len(Target) > 2 Then
        
            MsgBox "sorry too many characters"
            Target.ClearContents
        Else
        Target.Formula = UCase(Target.Formula)
            Exit Sub

  End If
  End If
End Sub

Maybe just with validation

=AND(LEN(A1)<=2,ISTEXT(A1),ISERROR(SEARCH("/",A1)),ISERROR(SEARCH(".",A1)),ISERROR(SEARCH(" ",A1)))

VBA Noob

Originally Posted by whitSC

I don't know why, but the ISTEXT function doesn't work at all. No matter what I put in, with the exception of numbers only, it accepts the data and registers True.

The VBA macro works fine. I had to modify it a little to restrict it to 2-3 letters as shown.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If Len(Target) = 1 Then
            MsgBox "Min. 2 letters"
        ElseIf Len(Target) > 3 Then
            MsgBox "Max. 3 letters"
            Target.ClearContents
        Else
        Target.Formula = UCase(Target.Formula)
            Exit Sub
        End If
        End If
End Sub

Hi,
I just tried the data validation as described in my first post and it works for me