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!

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!

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!

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!

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!