Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 37

Thread: Allow only certain alphanumeric data in a range

  1. #11
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,485
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    What range are you working with? the formula you posted checked A11 so that is what I used. The formula should use the first cell in the range. So if it is for the whole column then use A1 if it starts in A11 then use A11 in the formula.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  2. #12
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,917
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Quote Originally Posted by patni View Post
    Hey Special k99,

    Thank you for your reply.
    However it is preventing me from entering any data in it.
    If i try to write "OR12" , then it does not allow me. I am not able to entry any data
    You need to put an = before the formula
    I've used A1, adjust the cell in the formula to where you are storing the data.
    Last edited by Special-K99; May 16th, 2019 at 10:30 AM.

  3. #13
    Board Regular
    Join Date
    Feb 2006
    Location
    NJ, USA
    Posts
    330
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Allow only certain alphanumeric data in a range

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Value = "" Then Exit Sub
        If IsNumeric(Right(Target.Value, Len(Target.Value) - 2)) And _
            (Left(Target.Value) = "AK" Or Left(Target.Value) = "OR") Then
        Else
            MsgBox "Wrong Code Entered"
            
            'to set the cell back to blank
            Target.Value = ""
        End If
     
    End Sub
    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    Also, when asking questions, please be as detailed as possible, and If I have helped you, please let me know.
    I don't require thanks, but acknowledgement is nice

  4. #14
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Mick,

    Also if I enter any random data suppose "DR56" , then it is accepting it.
    It should not be accepting it but.

    Could you please check once.

    THANK YOU

  5. #15
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Special K99,
    i made it to this.

    Code:
    =AND(OR(LEFT(A99,2)="AK",LEFT(A99,2)="OR"),RIGHT(A99,LEN(A99)-2)+0>=1,RIGHT(A99,LEN(A99)-2)+0<=100)
    However 2 problem I am facing,

    1st is that i need to mearge your code with the
    Code:
    =COUNTIF($A:$A,A11)=1
    This is to prevent duplicate entries
    2nd is that suppose I enter OR (SPACE) 50 , then it is accepting.
    It would be better if it could allow only OR50 . It should show error even if it contains a space

  6. #16
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Scott,

    I will just do that and update you on it.
    Thank you for the guidance, It means a lot.
    Thank you

  7. #17
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,917
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Try

    =AND(OR(A99="OR 50",AND(OR(LEFT(A99,2)="AK",LEFT(A99,2)="OR"),RIGHT(A99,LEN(A99)-2)+0>=1,RIGHT(A99,LEN(A99)-2)+0<=100)),COUNTIF(A$1:A$1000,A99)=1)

  8. #18
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,681
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    10 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Maybe this:-
    NB:- This code should also prevent duplicates
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, Dn As Range, n As Long, Ray As Variant, Dic As Object, Num As Long
    Dim WS      As Worksheet
    Dim shp     As Shape
    Dim addr    As Variant
    Dim i As Long
    Dim lastRow As Long
    Dim tRng    As Range
    Dim shpName As String
    Dim shpPath As String
    Dim filepath As String
    If Target.Column = 3 Then
        Application.EnableEvents = False
        Cells(Target.Row, 17).Value = Date + Time
        Application.EnableEvents = True
    
    
    
    
    Set WS = ActiveSheet
    shpPath = filepath
    If Right(shpPath, 1) <> Application.PathSeparator Then shpPath = shpPath & Application.PathSeparator
    If Dir(shpPath) = "" Then MsgBox shpPath & " is invalid!", vbCritical, "INVALID PATH": Exit Sub
    
    
    lastRow = WorksheetFunction.Max(8, WS.Cells(Rows.Count, "A").End(xlUp).Row + 2)
    If Not Intersect(Target, Range("A8:A" & lastRow)) Is Nothing Then
        Set tRng = Cells(Target.Row, "T")
        On Error Resume Next
        If Len(Trim(Target.Value)) = 0 Then
            On Error Resume Next
            Me.Shapes("PictureAt" & tRng.Address).Delete
            Err.Clear
            On Error GoTo 0
        Else
            Err.Clear
            On Error GoTo 0
            shpName = Target.Value
            If Len(Trim(shpName)) > 0 Then
                With Application
                    .ScreenUpdating = False
                    .EnableEvents = False
                End With
                With tRng
                    .RowHeight = 56
                    .ClearContents
                    On Error Resume Next
                    Me.Shapes("PictureAt" & .Address).Delete
                    On Error GoTo 0
                End With
                If Dir(shpPath & Target & ".jpg") <> "" Then   '*  verify that the file exists
                    With tRng
                        Set shp = Me.Shapes.AddPicture(shpPath & shpName & ".jpg", msoFalse, msoCTrue, .Left, .Top, .Width, .Height)
                        shp.Name = "PictureAt" & .Address
                    End With
                Else
                    tRng.Value = "NO IMAGE" & Chr(10) & "FOUND"
                End If
            End If
        End If
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        If (Intersect(Target, Range("A1:G2")) Is Nothing) Then Exit Sub
        If Range("A7:Z500000").CurrentRegion.Rows.Count > 1 Then
            Range("A7:Z500000").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:G2")
        End If
    End With
    
    
    ElseIf Target.Column = 1 Then
    
    Set Rng = Range("A1:A100")
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
           Set Dic = CreateObject("scripting.dictionary")
                    Dic.CompareMode = vbTextCompare
                    Ray = Array("AK", "OR")
                    For n = 0 To 1
                        For Num = 1 To 100
                            Dic(Ray(n) & Num) = Empty
                        Next Num
                    Next n
            End If
        Application.EnableEvents = False
        If Target.Count = 1 Then
        With CreateObject("scripting.dictionary")
                  .CompareMode = vbTextCompare
                For Each Dn In Rng
                   If Not Dn.Value = vbNullString Then
                     If Not .exists(Dn.Value) Then
                       .Add (Dn.Value), ""
                    Else
                        Dn.Value = ""
                        MsgBox "Duplicate Entry"
                    End If
                 End If
                Next Dn
         End With
         If Not Target.Value = vbNullString Then
            If Not Dic.exists(Target.Value) Then
                Target = ""
                MsgBox "Invalid Entry"
            End If
         End If
         Application.EnableEvents = True
    End If
    
    End Sub
    Regards Mick

  9. #19
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Special K99,
    Will it do for other alpha numeric datas also?

    Example : If i write AK (SPACE) 56 or OR (SPACE) 91 etc, will it prevent them also.
    I mean I need it to be without spaces. I dont want it to allow spaces in between for all data

  10. #20
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Mike,
    I guess there os something wrong because it is accepting any data as of now.
    I entered "KL1" and it allowed

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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