Alphabet input only

[malbiar]

How can I restrict cells to allow only alphabet character entry only? I cannot figure out a possible formula in Data Validation and formatting the cells to General or Text allows other non-alphabet characters. Thanks in advance.

 

[JoeMo]

Are you willing to use VBA for this?

 

[malbiar]

I prefer to do without if possible.

 

[JoeMo]

Thanks for the reply. I can't see a way to do this with Data Validation, but maybe someone else can.

 

[malbiar]

I can try VBA for now if you don't mind sharing step by step details please.

 

[JoeMo]

Sure. You need to install the code below by following these steps:
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("A")) Is Nothing Then  'Replace Columns("A") with whatever range you want to apply this to
    Application.EnableEvents = False
    For Each c In Target
        If c.Value <> "" Then
            For i = 1 To Len(c.Value)
                If Mid(c.Value, i, 1) Like "[!A-Za-z]" Then
'                    c.Value = ""
                    Application.Undo
                    MsgBox "Sorry - only alpha characters allowed in column A cell entries." & vbLf & vbLf & _
                    "If you are entering more than one cell, any non-alpha character will cause all entries to revert to their pre-entry values"
                    Application.EnableEvents = True
                    Exit Sub
                End If
            Next i
        End If
    Next c
    Application.EnableEvents = True
End If
End Sub

EDIT: Forgot to mention that this code is set to monitor the whole of column A of the relevant worksheet. You can change the range (in red font above) to any range you wish to monitor.

 

[malbiar]

Thank you. I'm new to all this. How do I enable macros?

 

[JoeMo]

I'm using Excel 2010, but will be similar for other versions.File>Options>Trust Center (see note below)>Trust Center Settings>Trusted Locations>Add New Location

Use a location like: C:\Users\Joe\
replacing Joe with your user name. You can add more sub-folders as desired.

Note: When you reach the Trust Center dialog box, under Macro Settings, check the "Disable all macros with notification" radio button.

 

[malbiar]

Thanks.

 

[Peter_SSs]

I prefer to do without if possible.

Try this in standard Data Validation -> Allow: Custom -> Formula:

=SUM(LEN(A1) - LEN(SUBSTITUTE(UPPER(A1), CHAR(ROW(INDIRECT("65:90"))), ""))) = LEN(A1)

This is for cell A1 but if applying to a range, select the range and use the active cell address where I have used A1.