|
|
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" <DanN[ at ]discussions.microsoft.com>
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:
[Quoted Text] > 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" <DanN[ at ]discussions.microsoft.com> > 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" <DanN[ at ]discussions.microsoft.com>
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:
[Quoted Text] > 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" <DanN[ at ]discussions.microsoft.com> > 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:
[Quoted Text] > You are very welcome. > Jim Cone > > > "Dan N" <DanN[ at ]discussions.microsoft.com> > 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" <DanN[ at ]discussions.microsoft.com> > > 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" <Paige[ at ]discussions.microsoft.com>
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:
[Quoted Text] > You are very welcome.
> Jim Cone
>
>
> "Dan N" <DanN[ at ]discussions.microsoft.com>
> 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
|
|
Thanks for posting this solution. I had essentially the same problem and your
code has solved it for me.
"Jim Cone" wrote:
[Quoted Text] > 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" <Paige[ at ]discussions.microsoft.com> > 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" <DanN[ at ]discussions.microsoft.com> > > 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 > > |
|
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:
[Quoted Text] > 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" <DanN[ at ]discussions.microsoft.com> > 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! > |
|
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:
[Quoted Text] > 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" <Paige[ at ]discussions.microsoft.com> > 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" <DanN[ at ]discussions.microsoft.com> > > 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 > > |
|
|