Check for incorrect characters

[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

 

[Zone]

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

 

[Dave Peterson]

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

 

[Darren Hill]

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!

 

[Darren Hill]

Ah, excellent. So it is possible to stop the entry of illegal
characters.

Thanks, Dave.

Darren

 

[Dave Peterson]

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.

 

[Darren Hill]

Ooh, I'm glad you pointed that out.
I'll use both validation and this method, then.

Thanks for the warning,

Darren