Allow only certain alphanumeric data in a range

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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
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:

Richard U

Active Member
Joined
Feb 14, 2006
Messages
377
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
 

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
 

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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:
[COLOR=#333333]=COUNTIF($A:$A,A11)=1[/COLOR]
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
 

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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)
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Re: How to allow only certain alphanumeric data in a range

Maybe this:-
NB:- This code should also prevent duplicates
Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dic [COLOR="Navy"]As[/COLOR] Object, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] WS      [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] shp     [COLOR="Navy"]As[/COLOR] Shape
[COLOR="Navy"]Dim[/COLOR] addr    [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] lastRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] tRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] shpName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] shpPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] filepath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Column = 3 [COLOR="Navy"]Then[/COLOR]
    Application.EnableEvents = False
    Cells(Target.Row, 17).Value = Date + Time
    Application.EnableEvents = True




[COLOR="Navy"]Set[/COLOR] WS = ActiveSheet
shpPath = filepath
[COLOR="Navy"]If[/COLOR] Right(shpPath, 1) <> Application.PathSeparator [COLOR="Navy"]Then[/COLOR] shpPath = shpPath & Application.PathSeparator
[COLOR="Navy"]If[/COLOR] Dir(shpPath) = "" [COLOR="Navy"]Then[/COLOR] MsgBox shpPath & " is invalid!", vbCritical, "INVALID PATH": [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]


lastRow = WorksheetFunction.Max(8, WS.Cells(Rows.Count, "A").End(xlUp).Row + 2)
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("A8:A" & lastRow)) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] tRng = Cells(Target.Row, "T")
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] Len(Trim(Target.Value)) = 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        Me.Shapes("PictureAt" & tRng.Address).Delete
        Err.Clear
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] GoTo 0
    [COLOR="Navy"]Else[/COLOR]
        Err.Clear
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] GoTo 0
        shpName = Target.Value
        [COLOR="Navy"]If[/COLOR] Len(Trim(shpName)) > 0 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]With[/COLOR] Application
                .ScreenUpdating = False
                .EnableEvents = False
            [COLOR="Navy"]End[/COLOR] With
            [COLOR="Navy"]With[/COLOR] tRng
                .RowHeight = 56
                .ClearContents
                [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
                Me.Shapes("PictureAt" & .Address).Delete
                [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] GoTo 0
            [COLOR="Navy"]End[/COLOR] With
            If Dir(shpPath & Target & ".jpg") <> "" Then   '[COLOR="Green"][B]*  verify that the file exists[/B][/COLOR]
                [COLOR="Navy"]With[/COLOR] tRng
                    [COLOR="Navy"]Set[/COLOR] shp = Me.Shapes.AddPicture(shpPath & shpName & ".jpg", msoFalse, msoCTrue, .Left, .Top, .Width, .Height)
                    shp.Name = "PictureAt" & .Address
                [COLOR="Navy"]End[/COLOR] With
            [COLOR="Navy"]Else[/COLOR]
                tRng.Value = "NO IMAGE" & Chr(10) & "FOUND"
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]With[/COLOR] Application
    .ScreenUpdating = True
    .EnableEvents = True
    [COLOR="Navy"]If[/COLOR] (Intersect(Target, Range("A1:G2")) [COLOR="Navy"]Is[/COLOR] Nothing) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]If[/COLOR] Range("A7:Z500000").CurrentRegion.Rows.Count > 1 [COLOR="Navy"]Then[/COLOR]
        Range("A7:Z500000").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:G2")
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]ElseIf[/COLOR] Target.Column = 1 [COLOR="Navy"]Then[/COLOR]

[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:A100")
[COLOR="Navy"]If[/COLOR] Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/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] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
              .CompareMode = vbTextCompare
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
               [COLOR="Navy"]If[/COLOR] Not Dn.Value = vbNullString [COLOR="Navy"]Then[/COLOR]
                 [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                   .Add (Dn.Value), ""
                [COLOR="Navy"]Else[/COLOR]
                    Dn.Value = ""
                    MsgBox "Duplicate Entry"
                [COLOR="Navy"]End[/COLOR] If
             [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dn
     [COLOR="Navy"]End[/COLOR] With
     [COLOR="Navy"]If[/COLOR] Not Target.Value = vbNullString [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dic.exists(Target.Value) [COLOR="Navy"]Then[/COLOR]
            Target = ""
            MsgBox "Invalid Entry"
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]End[/COLOR] If
     Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] If

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

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
 

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,018
Messages
5,466,071
Members
406,463
Latest member
vstruggs

This Week's Hot Topics

Top