+ Reply to Thread
Results 1 to 6 of 6

Data Validation Formula

Hybrid View

  1. #1
    Dan N
    Guest

    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!

  2. #2
    Jim Cone
    Guest

    Re: 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
    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!

  3. #3
    Dan N
    Guest

    Re: 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
    > 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!
    >


  4. #4
    Jim Cone
    Guest

    Re: Data Validation Formula

    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!



  5. #5
    Paige
    Guest

    Re: Data Validation Formula

    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!

    >
    >


  6. #6
    Jim Cone
    Guest

    Re: 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
    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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1