Check for incorrect characters

  • Thread starter Thread starter Darren Hill
  • Start date Start date
D

Darren Hill

I have a textbox or two that I want to run some validation on.
Basically, I only want to allow numbers, or uppercase or lowercase
letters, or commas, spaces, apostrophes, and full stops. (, '.)

What code would I need to restrict entry to these possibilities?

Or failing that, after they have been entered, to validate against them
to report an error.

Thanks,

Darren
 
Darren, you do not say whether your textbox is on a userform or on a
worksheet. If it's on a userform and you have a command button to unload
the form, you could use something like this. Note that Str should be all on
one line with a space after the z. HTH, James
Private Sub CommandButton1_Click()
Const Str = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
0123456789,'."
Dim j As Integer, chr As String, Flag As Boolean
Flag = False
For j = 1 To Len(TextBox1)
chr = Mid(TextBox1, j, 1)
If Not InStr(Str, chr) > 0 Then Flag = True
Next j
If Flag Then
MsgBox "Only these characters are allowed: " & Str
Else
'do your stuff
Unload Me
End If
End Sub
 
This'll stop the typing of those characters:

Option Explicit
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)


Select Case KeyAscii
'numbers, letters, commas, spaces, apostrophes, and full stops
Case Asc("0") To Asc("9"), Asc("a") To Asc("z"), Asc("A") To Asc("Z"), _
Asc(","), Asc(" "), Asc("'"), Asc(".")
'ok
Case Else
KeyAscii = 0
Beep
End Select

End Sub
 
Nice suggestion. I thouyght of using Instr, but was doing it the other
way around: if Instr(textbox.text, "a"), then again for "b", and so on.
I knew there had to be a better way! :)
 
Ah, excellent. So it is possible to stop the entry of illegal
characters.

Thanks, Dave.

Darren
 
Well, maybe...

You notice that I wrote:
"This'll stop the typing of those characters:"

It doesn't stop the user from pasting anything into that textbox.
 
Ooh, I'm glad you pointed that out.
I'll use both validation and this method, then.

Thanks for the warning,

Darren
 
Back
Top