Data Validation - Accept Only Certain Numbers/Characters

The following code came from a previous question; purpose was to limit input
to only the numbers 1-9 and alpha characters. I need to modify it so that it
will accept a number anywhere from 0-100% and the letter 'M'. Have tried
various ways to change the 'Const str_Chars As String', such as
"[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the proper
way to designate the string?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OuttaHere
If Target.Address = "$B$5" Then '<<<< change cell
Application.EnableEvents = False
Dim strText As String
Dim lngN As Long
Const str_Chars As String = "[0-9a-zA-Z ]"
strText = Target.Text

For lngN = 1 To Len(strText)
If Not Mid$(strText, lngN, 1) Like str_Chars Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, " Blame Dan"
Application.Undo
Exit For
End If
Next 'lngN
End If
OuttaHere:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OuttaHere
If Target.Address = "$B$5" Then '<<<< change cell
Application.EnableEvents = False
With Target
If Not ((.Value >= 0 And .Value <= 1) Or UCase(.Value) = "M") Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, " Blame Dan"
Application.Undo
End If
End With
End If
OuttaHere:
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"Paige" wrote:

> The following code came from a previous question; purpose was to limit input
> to only the numbers 1-9 and alpha characters. I need to modify it so that it
> will accept a number anywhere from 0-100% and the letter 'M'. Have tried
> various ways to change the 'Const str_Chars As String', such as
> "[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the proper
> way to designate the string?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo OuttaHere
> If Target.Address = "$B$5" Then '<<<< change cell
> Application.EnableEvents = False
> Dim strText As String
> Dim lngN As Long
> Const str_Chars As String = "[0-9a-zA-Z ]"
> strText = Target.Text
>
> For lngN = 1 To Len(strText)
> If Not Mid$(strText, lngN, 1) Like str_Chars Then
> MsgBox "Only numbers or alphabetic characters allowed. ", _
> vbOKOnly, " Blame Dan"
> Application.Undo
> Exit For
> End If
> Next 'lngN
> End If
> OuttaHere:
> Application.EnableEvents = True
> End Sub
>

Jim,

Can you explain the use of the brackets in the line below from your previous
code? They seem to mean "inclusive" but I don't know how to google it and
find out more. I'd apprecaite any instruction on the general syntax:

Const str_Chars As String = "[0-9a-zA-Z ]"

thanks,

Doug

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo OuttaHere
> If Target.Address = "$B$5" Then '<<<< change cell
> Application.EnableEvents = False
> With Target
> If Not ((.Value >= 0 And .Value <= 1) Or UCase(.Value) = "M") Then
> MsgBox "Only numbers or alphabetic characters allowed. ", _
> vbOKOnly, " Blame Dan"
> Application.Undo
> End If
> End With
> End If
> OuttaHere:
> Application.EnableEvents = True
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Paige" wrote:
>
>> The following code came from a previous question; purpose was to limit
>> input
>> to only the numbers 1-9 and alpha characters. I need to modify it so
>> that it
>> will accept a number anywhere from 0-100% and the letter 'M'. Have tried
>> various ways to change the 'Const str_Chars As String', such as
>> "[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the
>> proper
>> way to designate the string?
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> On Error GoTo OuttaHere
>> If Target.Address = "$B$5" Then '<<<< change cell
>> Application.EnableEvents = False
>> Dim strText As String
>> Dim lngN As Long
>> Const str_Chars As String = "[0-9a-zA-Z ]"
>> strText = Target.Text
>>
>> For lngN = 1 To Len(strText)
>> If Not Mid$(strText, lngN, 1) Like str_Chars Then
>> MsgBox "Only numbers or alphabetic characters allowed. ", _
>> vbOKOnly, " Blame Dan"
>> Application.Undo
>> Exit For
>> End If
>> Next 'lngN
>> End If
>> OuttaHere:
>> Application.EnableEvents = True
>> End Sub
>>

That's not my code. To be perfectly honest a greater mind came up with that
one. I had no idea you could do that... Kinda cool though isn't it...
--
HTH...

Jim Thomlinson


"Doug Glancy" wrote:

> Jim,
>
> Can you explain the use of the brackets in the line below from your previous
> code? They seem to mean "inclusive" but I don't know how to google it and
> find out more. I'd apprecaite any instruction on the general syntax:
>
> Const str_Chars As String = "[0-9a-zA-Z ]"
>
> thanks,
>
> Doug
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > On Error GoTo OuttaHere
> > If Target.Address = "$B$5" Then '<<<< change cell
> > Application.EnableEvents = False
> > With Target
> > If Not ((.Value >= 0 And .Value <= 1) Or UCase(.Value) = "M") Then
> > MsgBox "Only numbers or alphabetic characters allowed. ", _
> > vbOKOnly, " Blame Dan"
> > Application.Undo
> > End If
> > End With
> > End If
> > OuttaHere:
> > Application.EnableEvents = True
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Paige" wrote:
> >
> >> The following code came from a previous question; purpose was to limit
> >> input
> >> to only the numbers 1-9 and alpha characters. I need to modify it so
> >> that it
> >> will accept a number anywhere from 0-100% and the letter 'M'. Have tried
> >> various ways to change the 'Const str_Chars As String', such as
> >> "[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the
> >> proper
> >> way to designate the string?
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> On Error GoTo OuttaHere
> >> If Target.Address = "$B$5" Then '<<<< change cell
> >> Application.EnableEvents = False
> >> Dim strText As String
> >> Dim lngN As Long
> >> Const str_Chars As String = "[0-9a-zA-Z ]"
> >> strText = Target.Text
> >>
> >> For lngN = 1 To Len(strText)
> >> If Not Mid$(strText, lngN, 1) Like str_Chars Then
> >> MsgBox "Only numbers or alphabetic characters allowed. ", _
> >> vbOKOnly, " Blame Dan"
> >> Application.Undo
> >> Exit For
> >> End If
> >> Next 'lngN
> >> End If
> >> OuttaHere:
> >> Application.EnableEvents = True
> >> End Sub
> >>
>
>
>

Check the Like operator in VBA Help.



--
Tim Williams
Palo Alto, CA


"Doug Glancy" <[email protected]> wrote in message news:[email protected]...
> Jim,
>
> Can you explain the use of the brackets in the line below from your previous
> code? They seem to mean "inclusive" but I don't know how to google it and
> find out more. I'd apprecaite any instruction on the general syntax:
>
> Const str_Chars As String = "[0-9a-zA-Z ]"
>
> thanks,
>
> Doug
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > On Error GoTo OuttaHere
> > If Target.Address = "$B$5" Then '<<<< change cell
> > Application.EnableEvents = False
> > With Target
> > If Not ((.Value >= 0 And .Value <= 1) Or UCase(.Value) = "M") Then
> > MsgBox "Only numbers or alphabetic characters allowed. ", _
> > vbOKOnly, " Blame Dan"
> > Application.Undo
> > End If
> > End With
> > End If
> > OuttaHere:
> > Application.EnableEvents = True
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Paige" wrote:
> >
> >> The following code came from a previous question; purpose was to limit
> >> input
> >> to only the numbers 1-9 and alpha characters. I need to modify it so
> >> that it
> >> will accept a number anywhere from 0-100% and the letter 'M'. Have tried
> >> various ways to change the 'Const str_Chars As String', such as
> >> "[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the
> >> proper
> >> way to designate the string?
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> On Error GoTo OuttaHere
> >> If Target.Address = "$B$5" Then '<<<< change cell
> >> Application.EnableEvents = False
> >> Dim strText As String
> >> Dim lngN As Long
> >> Const str_Chars As String = "[0-9a-zA-Z ]"
> >> strText = Target.Text
> >>
> >> For lngN = 1 To Len(strText)
> >> If Not Mid$(strText, lngN, 1) Like str_Chars Then
> >> MsgBox "Only numbers or alphabetic characters allowed. ", _
> >> vbOKOnly, " Blame Dan"
> >> Application.Undo
> >> Exit For
> >> End If
> >> Next 'lngN
> >> End If
> >> OuttaHere:
> >> Application.EnableEvents = True
> >> End Sub
> >>
>
>

Interesting. Thanks to both of you.

Doug

"Tim Williams" <timjwilliams at gmail dot com> wrote in message
news:[email protected]...
> Check the Like operator in VBA Help.
>
>
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Doug Glancy" <[email protected]> wrote in message
> news:[email protected]...
>> Jim,
>>
>> Can you explain the use of the brackets in the line below from your
>> previous
>> code? They seem to mean "inclusive" but I don't know how to google it
>> and
>> find out more. I'd apprecaite any instruction on the general syntax:
>>
>> Const str_Chars As String = "[0-9a-zA-Z ]"
>>
>> thanks,
>>
>> Doug
>>
>> "Jim Thomlinson" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > On Error GoTo OuttaHere
>> > If Target.Address = "$B$5" Then '<<<< change cell
>> > Application.EnableEvents = False
>> > With Target
>> > If Not ((.Value >= 0 And .Value <= 1) Or UCase(.Value) = "M")
>> > Then
>> > MsgBox "Only numbers or alphabetic characters allowed. ", _
>> > vbOKOnly, " Blame Dan"
>> > Application.Undo
>> > End If
>> > End With
>> > End If
>> > OuttaHere:
>> > Application.EnableEvents = True
>> > End Sub
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Paige" wrote:
>> >
>> >> The following code came from a previous question; purpose was to limit
>> >> input
>> >> to only the numbers 1-9 and alpha characters. I need to modify it so
>> >> that it
>> >> will accept a number anywhere from 0-100% and the letter 'M'. Have
>> >> tried
>> >> various ways to change the 'Const str_Chars As String', such as
>> >> "[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the
>> >> proper
>> >> way to designate the string?
>> >>
>> >> Private Sub Worksheet_Change(ByVal Target As Range)
>> >> On Error GoTo OuttaHere
>> >> If Target.Address = "$B$5" Then '<<<< change cell
>> >> Application.EnableEvents = False
>> >> Dim strText As String
>> >> Dim lngN As Long
>> >> Const str_Chars As String = "[0-9a-zA-Z ]"
>> >> strText = Target.Text
>> >>
>> >> For lngN = 1 To Len(strText)
>> >> If Not Mid$(strText, lngN, 1) Like str_Chars Then
>> >> MsgBox "Only numbers or alphabetic characters allowed. ", _
>> >> vbOKOnly, " Blame Dan"
>> >> Application.Undo
>> >> Exit For
>> >> End If
>> >> Next 'lngN
>> >> End If
>> >> OuttaHere:
>> >> Application.EnableEvents = True
>> >> End Sub
>> >>
>>
>>
>
>

If you need to do this type of text matching then you should check out the RegExp object. It has much more powerful capabilities.

Eg:
http://visualbasic.about.com/od/usin...l/blregexa.htm

The VB6 stuff is more or less the same in VBA.

--
Tim Williams
Palo Alto, CA


"Doug Glancy" <[email protected]> wrote in message news:%[email protected]...
> Interesting. Thanks to both of you.
>
> Doug
>
> "Tim Williams" <timjwilliams at gmail dot com> wrote in message
> news:[email protected]...
> > Check the Like operator in VBA Help.
> >
> >
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "Doug Glancy" <[email protected]> wrote in message
> > news:[email protected]...
> >> Jim,
> >>
> >> Can you explain the use of the brackets in the line below from your
> >> previous
> >> code? They seem to mean "inclusive" but I don't know how to google it
> >> and
> >> find out more. I'd apprecaite any instruction on the general syntax:
> >>
> >> Const str_Chars As String = "[0-9a-zA-Z ]"
> >>
> >> thanks,
> >>
> >> Doug
> >>
> >> "Jim Thomlinson" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> > Private Sub Worksheet_Change(ByVal Target As Range)
> >> > On Error GoTo OuttaHere
> >> > If Target.Address = "$B$5" Then '<<<< change cell
> >> > Application.EnableEvents = False
> >> > With Target
> >> > If Not ((.Value >= 0 And .Value <= 1) Or UCase(.Value) = "M")
> >> > Then
> >> > MsgBox "Only numbers or alphabetic characters allowed. ", _
> >> > vbOKOnly, " Blame Dan"
> >> > Application.Undo
> >> > End If
> >> > End With
> >> > End If
> >> > OuttaHere:
> >> > Application.EnableEvents = True
> >> > End Sub
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "Paige" wrote:
> >> >
> >> >> The following code came from a previous question; purpose was to limit
> >> >> input
> >> >> to only the numbers 1-9 and alpha characters. I need to modify it so
> >> >> that it
> >> >> will accept a number anywhere from 0-100% and the letter 'M'. Have
> >> >> tried
> >> >> various ways to change the 'Const str_Chars As String', such as
> >> >> "[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the
> >> >> proper
> >> >> way to designate the string?
> >> >>
> >> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> >> On Error GoTo OuttaHere
> >> >> If Target.Address = "$B$5" Then '<<<< change cell
> >> >> Application.EnableEvents = False
> >> >> Dim strText As String
> >> >> Dim lngN As Long
> >> >> Const str_Chars As String = "[0-9a-zA-Z ]"
> >> >> strText = Target.Text
> >> >>
> >> >> For lngN = 1 To Len(strText)
> >> >> If Not Mid$(strText, lngN, 1) Like str_Chars Then
> >> >> MsgBox "Only numbers or alphabetic characters allowed. ", _
> >> >> vbOKOnly, " Blame Dan"
> >> >> Application.Undo
> >> >> Exit For
> >> >> End If
> >> >> Next 'lngN
> >> >> End If
> >> >> OuttaHere:
> >> >> Application.EnableEvents = True
> >> >> End Sub
> >> >>
> >>
> >>
> >
> >
>
>

Thanks, everyone! Will use this. FYI, the code I was referencing came from
a Jim Cone, in a previous post in this database, under data validation. Have
a great weekend and thanks again for all your help and suggestions.

"Paige" wrote:

> The following code came from a previous question; purpose was to limit input
> to only the numbers 1-9 and alpha characters. I need to modify it so that it
> will accept a number anywhere from 0-100% and the letter 'M'. Have tried
> various ways to change the 'Const str_Chars As String', such as
> "[0.00-100.00%,m,M]" etc., to no avail. Can someone please advise the proper
> way to designate the string?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo OuttaHere
> If Target.Address = "$B$5" Then '<<<< change cell
> Application.EnableEvents = False
> Dim strText As String
> Dim lngN As Long
> Const str_Chars As String = "[0-9a-zA-Z ]"
> strText = Target.Text
>
> For lngN = 1 To Len(strText)
> If Not Mid$(strText, lngN, 1) Like str_Chars Then
> MsgBox "Only numbers or alphabetic characters allowed. ", _
> vbOKOnly, " Blame Dan"
> Application.Undo
> Exit For
> End If
> Next 'lngN
> End If
> OuttaHere:
> Application.EnableEvents = True
> End Sub
>