Data Validation Formula
 

Geetings,

I'm trying to use data validation to only allow letters or numbers to be
entered in a cell (I don't want the user to enter any symbols). Can you help
me write a formula for that?

Any help would be GREATLY appreciated!

Data Validation Formula
 

Since you posted in the programming group, how about some code.
The code below goes in the module for the sheet in question.
Access that by right-clicking the sheet tab and selecting "View Code".
Copy and paste the code in there.
It works only on cell B5, change that to the cell desired.
See the line marked with <<<<.
Only spaces, numbers and upper and lower case letters are allowed.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'--------------------
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
'--------------



"Dan N"
wrote in message
Greetings,

I'm trying to use data validation to only allow letters or numbers to be
entered in a cell (I don't want the user to enter any symbols). Can you help
me write a formula for that?

Any help would be GREATLY appreciated!

Data Validation Formula
 

Jim,

That's outstanding! The code worked perfectly! After working on this for so
long I can't begin to tell you how much I appreciate the help. I really loved
the "Blame Dan" part. It made me laugh. I'm keeping it!

Great job. Thanks. - Dan N

"Jim Cone" wrote:

Since you posted in the programming group, how about some code.
The code below goes in the module for the sheet in question.
Access that by right-clicking the sheet tab and selecting "View Code".
Copy and paste the code in there.
It works only on cell B5, change that to the cell desired.
See the line marked with <<<<.
Only spaces, numbers and upper and lower case letters are allowed.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'--------------------
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
'--------------



"Dan N"
wrote in message
Greetings,

I'm trying to use data validation to only allow letters or numbers to be
entered in a cell (I don't want the user to enter any symbols). Can you help
me write a formula for that?

Any help would be GREATLY appreciated!

Data Validation Formula
 

You are very welcome.
Jim Cone


"Dan N"
wrote in message...
Jim,
That's outstanding! The code worked perfectly! After working on this for so
long I can't begin to tell you how much I appreciate the help. I really loved
the "Blame Dan" part. It made me laugh. I'm keeping it!
Great job. Thanks. - Dan N



"Jim Cone" wrote:
Since you posted in the programming group, how about some code.
The code below goes in the module for the sheet in question.
Access that by right-clicking the sheet tab and selecting "View Code".
Copy and paste the code in there.
It works only on cell B5, change that to the cell desired.
See the line marked with <<<<.
Only spaces, numbers and upper and lower case letters are allowed.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'--------------------
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
'--------------



"Dan N"
wrote in message
Greetings,
I'm trying to use data validation to only allow letters or numbers to be
entered in a cell (I don't want the user to enter any symbols). Can you help
me write a formula for that?
Any help would be GREATLY appreciated!

Data Validation Formula
 

Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - San Francisco, USA - 04/21/2006
On Error GoTo OuttaHere
If Target.Address = "$B$5" Then '<<<< change cell
Application.EnableEvents = False
Dim varValue As Variant
Const str_Chars As String = "[mM]"
varValue = Target.Value

Select Case True
Case varValue Like str_Chars
Case IsNumeric(varValue)
Select Case True
Case Val(varValue) < 0
MsgBox "Bad"
Application.Undo
Case Val(varValue) 100 '<<<< Or 1 ?
MsgBox "Bad"
Application.Undo
End Select
Case Else
MsgBox "Bad"
Application.Undo
End Select

End If
OuttaHe
Application.EnableEvents = True
End Sub

'If things stop working...
Sub Reinstate()
Application.EnableEvents = True
End Sub
'-----------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(some free add-ins, some for sale)



"Paige"
wrote in message
Jim, I'm trying to adjust your code to allow only the letter 'm' or 'M', and
any 2 decimal number between 0 and 100 (inclusive), formatted as percentage,
but so far have not been successful. I get it to recognize 'm' or 'M', but
not the numeric part; it still lets me enter any number. Can you advise how
to adjust?


"Jim Cone" wrote:
You are very welcome.
Jim Cone


"Dan N"
wrote in message...
Jim,
That's outstanding! The code worked perfectly! After working on this for so
long I can't begin to tell you how much I appreciate the help. I really loved
the "Blame Dan" part. It made me laugh. I'm keeping it!
Great job. Thanks. - Dan N

Data Validation Formula
 

Thanks for posting this solution. I had essentially the same problem and your
code has solved it for me.

"Jim Cone" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - San Francisco, USA - 04/21/2006
On Error GoTo OuttaHere
If Target.Address = "$B$5" Then '<<<< change cell
Application.EnableEvents = False
Dim varValue As Variant
Const str_Chars As String = "[mM]"
varValue = Target.Value

Select Case True
Case varValue Like str_Chars
Case IsNumeric(varValue)
Select Case True
Case Val(varValue) < 0
MsgBox "Bad"
Application.Undo
Case Val(varValue) 100 '<<<< Or 1 ?
MsgBox "Bad"
Application.Undo
End Select
Case Else
MsgBox "Bad"
Application.Undo
End Select

End If
OuttaHe
Application.EnableEvents = True
End Sub

'If things stop working...
Sub Reinstate()
Application.EnableEvents = True
End Sub
'-----------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(some free add-ins, some for sale)



"Paige"
wrote in message
Jim, I'm trying to adjust your code to allow only the letter 'm' or 'M', and
any 2 decimal number between 0 and 100 (inclusive), formatted as percentage,
but so far have not been successful. I get it to recognize 'm' or 'M', but
not the numeric part; it still lets me enter any number. Can you advise how
to adjust?


"Jim Cone" wrote:
You are very welcome.
Jim Cone


"Dan N"
wrote in message...
Jim,
That's outstanding! The code worked perfectly! After working on this for so
long I can't begin to tell you how much I appreciate the help. I really loved
the "Blame Dan" part. It made me laugh. I'm keeping it!
Great job. Thanks. - Dan N

Data Validation Formula
 

It looks that your code is headed in my direction. changeing range etc. Want
to require data input into all cells in the row. if blank donot allow move to
another cell
possible ? Have not found this thru excel data validation.
Thanks

"Jim Cone" wrote:

Since you posted in the programming group, how about some code.
The code below goes in the module for the sheet in question.
Access that by right-clicking the sheet tab and selecting "View Code".
Copy and paste the code in there.
It works only on cell B5, change that to the cell desired.
See the line marked with <<<<.
Only spaces, numbers and upper and lower case letters are allowed.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'--------------------
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
'--------------



"Dan N"
wrote in message
Greetings,

I'm trying to use data validation to only allow letters or numbers to be
entered in a cell (I don't want the user to enter any symbols). Can you help
me write a formula for that?

Any help would be GREATLY appreciated!

Data Validation Formula
 

Hello Jim,
your code was almost perfect except that i want to let the user input a
combination of numbers and letters such as: 1ft or 2ft up to 100ft for US
and 1m or 2m up to 100m for metric. Is there a way you could help me with
this dilema?

thank you in advance.
TG

"Jim Cone" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - San Francisco, USA - 04/21/2006
On Error GoTo OuttaHere
If Target.Address = "$B$5" Then '<<<< change cell
Application.EnableEvents = False
Dim varValue As Variant
Const str_Chars As String = "[mM]"
varValue = Target.Value

Select Case True
Case varValue Like str_Chars
Case IsNumeric(varValue)
Select Case True
Case Val(varValue) < 0
MsgBox "Bad"
Application.Undo
Case Val(varValue) 100 '<<<< Or 1 ?
MsgBox "Bad"
Application.Undo
End Select
Case Else
MsgBox "Bad"
Application.Undo
End Select

End If
OuttaHe
Application.EnableEvents = True
End Sub

'If things stop working...
Sub Reinstate()
Application.EnableEvents = True
End Sub
'-----------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(some free add-ins, some for sale)



"Paige"
wrote in message
Jim, I'm trying to adjust your code to allow only the letter 'm' or 'M', and
any 2 decimal number between 0 and 100 (inclusive), formatted as percentage,
but so far have not been successful. I get it to recognize 'm' or 'M', but
not the numeric part; it still lets me enter any number. Can you advise how
to adjust?


"Jim Cone" wrote:
You are very welcome.
Jim Cone


"Dan N"
wrote in message...
Jim,
That's outstanding! The code worked perfectly! After working on this for so
long I can't begin to tell you how much I appreciate the help. I really loved
the "Blame Dan" part. It made me laugh. I'm keeping it!
Great job. Thanks. - Dan N

Data Validation Formula
 

TG,
My original code, posted on 03/31/2006 should do what you want.
The "Like" operator does the work, see below ...

'--
Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - Portland, Oregon USA
'Only spaces, numbers and upper/lower case letters
'can be entered in the specified cell. Code goes in the Sheet module.

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 TG"
Application.Undo
Exit For
End If
Next 'lngN
End If

OuttaHe
Application.EnableEvents = True
End Sub
'--
Jim Cone
Portland, Oregon USA
(thanks in advance is no thanks)



"TG"

wrote in message
Hello Jim,
your code was almost perfect except that i want to let the user input a
combination of numbers and letters such as: 1ft or 2ft up to 100ft for US
and 1m or 2m up to 100m for metric. Is there a way you could help me with
this dilema?
thank you in advance.
TG

Data Validation Formula
 

You don't have to do a loop through each character to test the original
string for non-alphanumeric characters...

Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - Portland, Oregon USA
'Only spaces, numbers and upper/lower case letters
'can be entered in the specified cell. Code goes in the Sheet module.
On Error GoTo OuttaHere
If Target.Address = "$B$5" Then '<<<< change cell
Application.EnableEvents = False
If Target.Text Like "*[!0-9a-zA-Z]*" Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, " Blame TG"
Application.Undo
End If
End If

OuttaHe
Application.EnableEvents = True
End Sub

Given what is being done in this procedure, I'm don't think you need the
protection of the On Error GoTo statement either; I think this simplified
version of the above should work fine...

Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - Portland, Oregon USA
'Only spaces, numbers and upper/lower case letters
'can be entered in the specified cell. Code goes in the Sheet module.
If Target.Address = "$B$5" Then '<<<< change cell
If Target.Text Like "*[!0-9a-zA-Z]*" Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, " Blame TG"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
End Sub

--
Rick (MVP - Excel)


"Jim Cone" wrote in message
...
TG,
My original code, posted on 03/31/2006 should do what you want.
The "Like" operator does the work, see below ...

'--
Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - Portland, Oregon USA
'Only spaces, numbers and upper/lower case letters
'can be entered in the specified cell. Code goes in the Sheet module.

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 TG"
Application.Undo
Exit For
End If
Next 'lngN
End If

OuttaHe
Application.EnableEvents = True
End Sub
'--
Jim Cone
Portland, Oregon USA
(thanks in advance is no thanks)



"TG"

wrote in message
Hello Jim,
your code was almost perfect except that i want to let the user input a
combination of numbers and letters such as: 1ft or 2ft up to 100ft for US
and 1m or 2m up to 100m for metric. Is there a way you could help me with
this dilema?
thank you in advance.
TG

Data Validation Formula
 

Thank you very much Jim, your code works find now all i need to do is limit
the user to be able to input only 1ft or 1m but i think i may now know what
to do.

"Jim Cone" wrote:

TG,
My original code, posted on 03/31/2006 should do what you want.
The "Like" operator does the work, see below ...

'--
Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - Portland, Oregon USA
'Only spaces, numbers and upper/lower case letters
'can be entered in the specified cell. Code goes in the Sheet module.

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 TG"
Application.Undo
Exit For
End If
Next 'lngN
End If

OuttaHe
Application.EnableEvents = True
End Sub
'--
Jim Cone
Portland, Oregon USA
(thanks in advance is no thanks)



"TG"

wrote in message
Hello Jim,
your code was almost perfect except that i want to let the user input a
combination of numbers and letters such as: 1ft or 2ft up to 100ft for US
and 1m or 2m up to 100m for metric. Is there a way you could help me with
this dilema?
thank you in advance.
TG