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