Userform Data Validation

[bethaneesmith]

I need the text box in the userform to accept only alphabet letters. This is the code I have so far but it only accepts 2 letters. Also, the message box telling the user to only enter letters pops up when the userform is initialized and after the text is inputted. How do I fix this?

Private Sub TextBox1_Change()
If Not TextBox1.Value = vbNullString _
And Not TextBox1.Value Like "[A-Z]" _
And Not TextBox1.Value Like "[a-z]" _
And Not TextBox1.Value Like "[A-Z][A-Z]" _
And Not TextBox1.Value Like "[a-z][a-z]" _
And Not TextBox1.Value Like "[A-Z][a-z]" _
And Not TextBox1.Value Like "[a-z][A-Z]" Then
TextBox1.Value = vbNullString
MsgBox "Enter only letters.", 0, vbNullString"
End If
End Sub

 

[Colo]

Hi bethaneesmith, welcome to the forum. To check the strings, using Regular Expression is handy as follows.

Private Sub TextBox1_Change()
     Dim oReg As Object
     Set oReg = CreateObject("VBScript.RegExp")
     oReg.Pattern = "[^a-zA-Z]"
     If Not oReg.Test(TextBox1.Value) = False Then
     TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
         MsgBox "Enter only letters.", 0
     End If
     Set objRE = Nothing
End Sub