Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Thread: Allow only certain alphanumeric data in a range

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

    Default Allow only certain alphanumeric data in a range

    Hey guys,
    I need a small help.

    In my sheet "PRODUCT" range "A1:A150" I want it to only only certain alphanumeric data.
    I want it to allow only "AK1, AK2, AK3, AK4, AK5 ....... AK100" and "OR1, OR2, OR3, OR4, OR5 ..... OR100"

    If a user enters anything other than this, then I dont want excel to allow it. Probably give a error message saying "Wrong Code Entered"

    Also , I already have this in my data valididation
    Code:
    =COUNTIF($A:$A,A11)=1
    . This prevents duplicate entries

    Can anyone please help me.

    THANK YOU
    Last edited by patni; May 16th, 2019 at 09:25 AM.

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

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

    If your happy to use a VbA "Change Event , try this:-
    To load code:-
    Right click sheet tab >> Select View Code>> Vb window appears>> Paste code into vbWindow>> close Vb Window.
    To run code enter data in any cell "A1 A100".
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, n As Long, Dic As Object, Ray As Variant, Num As Long
    Set Rng = Range("A1:A100")
        If Not Intersect(Target, Rng) Is Nothing Then
            If Dic Is Nothing Then
                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 Not Dic.exists(Target.Value) Then
            MsgBox "Entry Not Valid"
            Target = ""
         End If
         Application.EnableEvents = True
    End If
    
    End Sub
    Regards Mick

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

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

    In Data validation try
    Code:
    =AND(COUNTIF($A:$A,A11)=1,OR(LEFT(A11,2)="AK",LEFT(A11,2)="OR"),--RIGHT(A11,LEN(A11)-2)>=1,ISNUMBER(--RIGHT(A11,LEN(A11)-2)),--RIGHT(A11,LEN(A11)-2)<=100)
    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.

  4. #4
    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,

    Sure . Thank you so much. Thank you.
    I will just try loading the code and let you know the result.

    Thank you for your reply

  5. #5
    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,
    Thank you so much for your reply
    Will this also prevent duplicate entries in the range ?

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

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

    Yes, it has your original prevent duplicate 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.

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

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

    Try Data Validation

    Custom Formula

    AND(OR(LEFT(A1,2)="AK",LEFT(A1,2)="OR"),RIGHT(A1,LEN(A1)-2)+0>=1,RIGHT(A1,LEN(A1)-2)+0<=100)

  8. #8
    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,

    I already have this worksheet change code in my workbook

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        Application.EnableEvents = False
        Cells(Target.Row, 17).Value = Date + Time
        Application.EnableEvents = True
    End If
    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
    
    
    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
    End Sub
    Could you help me mearge the above code with yours ?
    Thank you

  9. #9
    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 ,
    It isnt working in data validition.
    If i write any random data suppose "kkkk" , then it is accepting it

    Ideally it should not be accepting it. It should be accepting only certain alphanumeric datas

    Could you please check once.

    THANK YOU SO MUCh
    Last edited by patni; May 16th, 2019 at 10:26 AM.

  10. #10
    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,

    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

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
  •