Allow only certain alphanumeric data in a range

[patni]

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

=COUNTIF($A:$A,A11)=1

. This prevents duplicate entries

Can anyone please help me.

THANK YOU

 

[MickG]

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".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Ray [COLOR="Navy"]As[/COLOR] Variant, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:A100")
    [COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dic [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
                Dic.CompareMode = vbTextCompare
                Ray = Array("AK", "OR")
                [COLOR="Navy"]For[/COLOR] n = 0 To 1
                    [COLOR="Navy"]For[/COLOR] Num = 1 To 100
                        Dic(Ray(n) & Num) = Empty
                    [COLOR="Navy"]Next[/COLOR] Num
                [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]End[/COLOR] If
     Application.EnableEvents = False
     [COLOR="Navy"]If[/COLOR] Not Dic.exists(Target.Value) [COLOR="Navy"]Then[/COLOR]
        MsgBox "Entry Not Valid"
        Target = ""
     [COLOR="Navy"]End[/COLOR] If
     Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Scott T]

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

In Data validation try

=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)

 

[patni]

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

 

[patni]

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 ?

 

[Scott T]

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

Yes, it has your original prevent duplicate formula.

 

[Special-K99]

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)

 

[patni]

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

Hey Mick,

I already have this worksheet change code in my workbook

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

 

[patni]

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

 

[patni]

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