VBA Excel 2010 verify userform textbox input as text

Learning VBA for Excel. I'm using Excel 2010.

I have a userform with an input text field named "TextName".  
When the user clicks a button, the content of the textbox is added to a new row at the bottom of a worksheet column.

my question:
How do I force the content to be text only?

Here's what I'm trying, but it does not work:
    If Not Application.Workbooks.IsText(TextName.Text) Then
        MsgBox "Please enter text only."
        TextName.SetFocus
        Exit Sub
    End If
tom_burkhardtAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Whatever is entered in a textbox is text.

Do you want to check that only a-z/A-Z are entered?
0
 
Shanan212Commented:
try this (checks for numbers, if not text is assumed)

If isnumeric(TextName.Text) Then

Open in new window

0
 
Martin LissOlder than dirtCommented:
    Dim lngIndex As Long
    For lngIndex = 1 To Len(TextName.Text)
        If IsNumeric(Mid(TextName.Text, lngIndex, 1)) Then
            MsgBox "Please enter text only."
            TextName.SetFocus
            Exit Sub
        End If
    Next

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
tom_burkhardtAuthor Commented:
@imnorie: yes, I want letters only. No numbers, no special characters.
0
 
Martin LissOlder than dirtCommented:
Please see my code.
0
 
tom_burkhardtAuthor Commented:
@MartinLiss: thanks, but your code would allow special characters (*&^%$#@  etc.) through
0
 
tom_burkhardtAuthor Commented:
thanks all, I got it. credit to imnorie for putting me on the right track

    If Not (TextName.Text Like "[A-Z,a-z]") Then
        MsgBox "Please enter text only."
        TextName.SetFocus
        Exit Sub
    End If
0
 
Martin LissOlder than dirtCommented:
Change line 3 to


        If Asc(UCase(Mid(TextName.Text, lngIndex, 1))) < 65 Or Asc(UCase(Mid(TextName.Text, lngIndex, 1))) > 90 Then
0
 
tom_burkhardtAuthor Commented:
my apologies to MartinLiss, I'd already given the points to inmorie.
After running the code in my accept note, yes it blocked numbers but it also blocked letters, which I should have checked more thoroughly.
However for anyone else with this question , here is a function that will validate input from a textbox to ensure letters only ...

Function IsLetter(strValue As String) As Boolean
  Dim myInt As Integer  
  IsLetter = True
 
  For myInt = 1 To Len(strValue)
    Select Case Asc(Mid(strValue, myInt, 1))
    Case 65 To 90, 97 To 122
      ' do nothing, leave as TRUE
    Case Else
      ' not a letter, return FALSE
      IsLetter = False
      Exit For
    End Select
  Next myInt
End Function


so in the calling Sub, you would have:
    If Not IsLetter(TextName.Text) Then
        MsgBox "Please enter text only."
        TextName.SetFocus
        Exit Sub
    End If
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.