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
OuttaHe
Application.EnableEvents = True
End Sub

Data Validation - Accept Only Certain Numbers/Characters
 

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
OuttaHe
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
OuttaHe
Application.EnableEvents = True
End Sub

Data Validation - Accept Only Certain Numbers/Characters
 

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" wrote in message
...
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
OuttaHe
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
OuttaHe
Application.EnableEvents = True
End Sub

Data Validation - Accept Only Certain Numbers/Characters
 

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" wrote in message
...
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
OuttaHe
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
OuttaHe
Application.EnableEvents = True
End Sub

Data Validation - Accept Only Certain Numbers/Characters
 

Check the Like operator in VBA Help.



--
Tim Williams
Palo Alto, CA


"Doug Glancy" wrote in message ...
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" wrote in message
...
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
OuttaHe
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
OuttaHe
Application.EnableEvents = True
End Sub

Data Validation - Accept Only Certain Numbers/Characters
 

Interesting. Thanks to both of you.

Doug

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Check the Like operator in VBA Help.



--
Tim Williams
Palo Alto, CA


"Doug Glancy" wrote in message
...
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" wrote in
message
...
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
OuttaHe
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
OuttaHe
Application.EnableEvents = True
End Sub

Data Validation - Accept Only Certain Numbers/Characters
 

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" wrote in message ...
Interesting. Thanks to both of you.

Doug

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Check the Like operator in VBA Help.



--
Tim Williams
Palo Alto, CA


"Doug Glancy" wrote in message
...
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" wrote in
message
...
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
OuttaHe
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
OuttaHe
Application.EnableEvents = True
End Sub

Data Validation - Accept Only Certain Numbers/Characters
 

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
OuttaHe
Application.EnableEvents = True
End Sub