Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependent Data Validation with Illegal Characters | Excel Discussion (Misc queries) | |||
How do i get Data Validation to accept another workbook? | Excel Discussion (Misc queries) | |||
Data Validation - Only Allowing certain characters | Excel Worksheet Functions | |||
Problem with Data Validation and limiting characters | Excel Worksheet Functions | |||
Data validation, alpha or numeric characters | Excel Programming |