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


"Darren Hill" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

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 = "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
>
>
> "Darren Hill" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>

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
>