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.

 

[malbiar]

@Peter_SSs...I get this error message: A named range you specified cannot be found. When I select a specific range where do I update that information in the formula? Thanks.

 

[malbiar]

Thank you JoeMo. I will try this today.

 

[Rick Rothstein]

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

You can write the above code without looping through the characters one at a time...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, 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
        If c.Value Like "*[!A-Za-z]*" Then
          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
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]

I left it where you had it originally, but my preference would be to move the Application.Undo statement from right before the MessageBox to immediately after it... that way the user can look at his/her entry to see what they actually typed before the cell reverts back to its previous value. If the OP reader of this thread agrees, then a Target.Select should be added immediately before the MessageBox so that the mistaken cell is highlighted for the user. Here is what that code would look like...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, 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
        If c.Value Like "*[!A-Za-z]*" Then
          Target.Select
          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.Undo
          Application.EnableEvents = True
          Exit Sub
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]

Note: Installation for the above code(s) is as Joe described in Message #6 .

 

[JoeMo]

You can write the above code without looping through the characters one at a time...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, 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
        If c.Value Like "*[!A-Za-z]*" Then
          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
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

I left it where you had it originally, but my preference would be to move the Application.Undo statement from right before the MessageBox to immediately after it... that way the user can look at his/her entry to see what they actually typed before the cell reverts back to its previous value. If the OP reader of this thread agrees, then a Target.Select should be added immediately before the MessageBox so that the mistaken cell is highlighted for the user. Here is what that code would look like...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, 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
        If c.Value Like "*[!A-Za-z]*" Then
          Target.Select
          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.Undo
          Application.EnableEvents = True
          Exit Sub
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Note: Installation for the above code(s) is as Joe described in Message #6 .

Good suggestions Rick - thanks.

 

[Peter_SSs]

I get this error message: A named range you specified cannot be found.

I'm not sure how you could get such a message from what I suggested.

When I select a specific range where do I update that information in the formula?

When you select your specific range, most of the cells in that range will be shaded but one will be unshaded - that is the active cell. Suppose that active cell is F19 then with your range still selected ..

Data ribbon tab -> Data Validation -> Data Validation... -> Clear All (if you previously had any DV in that range) -> Settings tab -> Allow: Custom -> Formula: =SUM(LEN(F19) - LEN(SUBSTITUTE(UPPER(F19), CHAR(ROW(INDIRECT("65:90"))), ""))) = LEN(F19) -> OK

 

[malbiar]

I figured it out. Thanks everyone!

 

[Peter_SSs]

I figured it out. Thanks everyone!

Good news.

Good suggestions Rick - thanks.

In case other readers are contemplating using the vba approach suggested in this thread, I have some further points and suggestion.

... that way the user can look at his/her entry to see what they actually typed before the cell reverts back to its previous value. If the OP reader of this thread agrees, then a Target.Select should be added immediately before the MessageBox so that the mistaken cell is highlighted for the user.

That would work if the user is entering one cell at a time, but the code is also written to work on a range of cells entered at once. In that circumstance, all of the entries would be highlighted, not just the invalid one(s). In fact, the invalid one(s) may not even be visible on the screen.

Here is what that code would look like...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, 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
        If c.Value Like "*[!A-Za-z]*" Then
          Target.Select
          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.Undo
          Application.EnableEvents = True
          Exit Sub
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]

Apart from the relatively minor issue mentioned above, this code still has, in my view, a significant double flaw. It can clear entries in ranges not included in the intended target range (column A as written) and it can clear a set of perfectly legitimate entries from column A. How can that happen? If the range E1:F2 below is copied and pasted anywhere in column A, the code will clear all 4 cells even though E1:E2 are legitimate entries in column A and F1:F2 are legitimate entries in column B.

Book1
	E	F
1	TEXTA	23
2	TEXTB	56
DV Letters vba

If multiple values are entered/pasted at once, my code below only removes the invalid ones (perhaps an advantage) but does not restore any previous value to those cells (perhaps a disadvantage - though with more effort I think previous values could be restored to those cells). Of course, if the user would prefer, the 'Undo' option could still be invoked.

Anyway, my version is:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, c As Range, rChanged As Range, rErrors As Range
  Set rChanged = Intersect(Target, Columns("A")) 'Replace Columns("A") with whatever range you want to apply this to
  If Not rChanged Is Nothing Then
    For Each c In rChanged
      If c.Value <> "" Then
        If c.Value Like "*[!A-Za-z]*" Then
          If rErrors Is Nothing Then
            Set rErrors = c
          Else
            Set rErrors = Union(c, rErrors)
          End If
        End If
      End If
    Next c
    If Not rErrors Is Nothing Then
      Application.Goto Reference:=rErrors, Scroll:=True
      MsgBox "Sorry - only alpha characters allowed in column A cell entries." & vbLf & vbLf & _
                 "The selected cell(s) contain invalid entries and will be cleared."
      Application.EnableEvents = False
      rErrors.ClearContents
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[Rick Rothstein]

In case other readers are contemplating using the vba approach suggested in this thread, I have some further points and suggestion.

I have modified the modified code suggestion I made in Message #13 to confine the testing to cell that were changed in Column A only. I did not change the way the Undo works because I like the idea of restoring the entire Target if cells changed in Column A contain the unwanted non-alpha characters. My thinking is this... if the user thought he was copying the correct multiple columns (correct because he/she thought the first column of the multiple column selection contained the proper alpha characters) but, in fact, grabbed the wrong columns and his/her first column contained non-alpha characters, then I would think the other columns are probably not the correct ones either, so I like reverting everything back to the way the cells looked before the attempted paste operation. That way, the user can either correct the data or find the correct columns of data and then re-copy/paste them (note, by the way, that the MessageBox says this is what will happen)... I don't think anything is really lost by requiring the user to do that. Anyway, here is my suggested modified code...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, c As Range
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, Columns("A"))
      If c.Value <> "" Then
        If c.Value Like "*[!A-Za-z]*" Then
          c.Select
          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.Undo
          Application.EnableEvents = True
          Exit Sub
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]