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!

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



"Dan N" <[email protected]>
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
> OuttaHere:
> Application.EnableEvents = True
> End Sub
> '--------------
>
>
>
> "Dan N" <[email protected]>
> 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" <[email protected]>
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
> OuttaHere:
> Application.EnableEvents = True
> End Sub
> '--------------
>
>
>
> "Dan N" <[email protected]>
> 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, 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" <[email protected]>
> 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
> > OuttaHere:
> > Application.EnableEvents = True
> > End Sub
> > '--------------
> >
> >
> >
> > "Dan N" <[email protected]>
> > 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!
>
>

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
OuttaHere:
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" <[email protected]>
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" <[email protected]>
> 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