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 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz
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


"Darren Hill" wrote in message
...
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

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
Zone wrote:
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 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz
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


"Darren Hill" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

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

Thanks, Dave.

Darren

Dave Peterson wrote:
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 wrote:
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

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 wrote:

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

Thanks, Dave.

Darren

Dave Peterson wrote:
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 wrote:
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


--

Dave Peterson

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

Thanks for the warning,

Darren
Dave Peterson wrote:
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 wrote:
Ah, excellent. So it is possible to stop the entry of illegal
characters.

Thanks, Dave.

Darren

Dave Peterson wrote:
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 wrote:
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