Data Validation - Accept Only Certain Numbers/Characters

P

Paige

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
 
J

Jim Thomlinson

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
 
D

Doug Glancy

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jim Thomlinson

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

Check the Like operator in VBA Help.



--
Tim Williams
Palo Alto, CA


Doug Glancy said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Doug Glancy

Interesting. Thanks to both of you.

Doug

Tim Williams said:
Check the Like operator in VBA Help.



--
Tim Williams
Palo Alto, CA


Doug Glancy said:
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

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
OuttaHere:
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


:

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
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

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/usingvbnet/l/blregexa.htm

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

--
Tim Williams
Palo Alto, CA


Doug Glancy said:
Interesting. Thanks to both of you.

Doug

Tim Williams said:
Check the Like operator in VBA Help.



--
Tim Williams
Palo Alto, CA


Doug Glancy said:
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

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
OuttaHere:
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


:

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
Click to expand...
Click to expand...
Click to expand...
 
P

Paige

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code for an entire column 3
Making a macro only work on certain files 2
Help in sorting data 2
Split Characters in Textbox 9
Bold items within text string 7
VBA for data validation in 3rd column does not run. I need help. 1
Extracting keywords from a string 8
Proper case module help! 6

Top