Setting Acceptable format for cells

[Wyvern]

I've tried goggle but think my search description is wrong...
I need to set a format in a given row to only accept data in a given format - DN###### or DNX##### (each of the entries will be unique) or but for some reason I'm struggling and my excel skills are rusty!

 

[Joe4]

Welcome to the Board!

I would probably use VBA to do that.
Right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    Dim validEntry As Boolean
    
'   Only run on entries made in row 2
    Set myRange = Intersect(Target, Rows("2"))
    If Not myRange Is Nothing Then
        For Each cell In myRange
            validEntry = True
'           Make sure entry is 8 characters and begins with "DN"
            If (Len(cell) <> 8) Or (Left(cell, 2) <> "DN") Then
                validEntry = False
            Else
                If Mid(cell, 3, 1) = "X" Then
                    If Not IsNumeric(Mid(cell, 4, 5)) Then validEntry = False
                Else
                    If Not IsNumeric(Mid(cell, 3, 6)) Then validEntry = False
                End If
            End If
'           Determine whether entry is valid
            If validEntry = False Then
                Application.EnableEvents = False
                cell.ClearContents
                Application.EnableEvents = True
                MsgBox "Entry must be in DN###### or DNX##### format", vbOKOnly, "INVALID ENTRY!"
            End If
        Next cell
    End If
                
End Sub

Just change the value of Rows("2") to match whatever row you want to apply this to.
This event procedure VBA code meaning that it runs automatically upon someone entering data into the sheet.

 

[wideboydixon]

It's unusual for me to comment on another solution so apologies in advance to @Joe4 but I couldn't help suggesting a small optimization of the code checking the validity of the entry. Couldn't you just use the pattern matches already provided?

        For Each cell In myRange
            validEntry = (cell Like "DN######") Or (cell Like "DNX#####")
'           Determine whether entry is valid
            If validEntry = False Then

IsNumeric doesn't guarantee numbers unfortunately. IsNumeric("-4") returns True.

WBD

 

[Joe4]

It's unusual for me to comment on another solution so apologies in advance to @Joe4 but I couldn't help suggesting a small optimization of the code checking the validity of the entry. Couldn't you just use the pattern matches already provided?

No need to apologize! If you see flaws or know a better way, please post it by all means! I love to see new/different/better methods, and your method is definitely better.

For the OP, here is what the complete updated code should look like:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    Dim validEntry As Boolean
    
'   Only run on entries made in row 2
    Set myRange = Intersect(Target, Rows("2"))
    If Not myRange Is Nothing Then
        For Each cell In myRange
            validEntry = (cell Like "DN######") Or (cell Like "DNX#####")
'           Determine whether entry is valid
            If validEntry = False Then
                Application.EnableEvents = False
                cell.ClearContents
                Application.EnableEvents = True
                MsgBox "Entry must be in DN###### or DNX##### format", vbOKOnly, "INVALID ENTRY!"
            End If
        Next cell
    End If
                
End Sub

 

[Wyvern]

Welcome to the Board!

I would probably use VBA to do that.

Thank you for the welcome!

And the VBA - unfortunately the organisation I'm working for frowns on macro enabled spreadsheets...

So I'm think it will have to be via custom data validation but can work out what the formula needs to be....?

 

[Joe4]

Admittedly, I haven't done much with Custom Data Validation.
Here is a write-up that shows a bunch of different examples, maybe that help you formulate what you need: https://www.ablebits.com/office-add...-data-validation-excel-custom-rules-formulas/

 

[wideboydixon]

I don't think you can do this with data validation without using an extra helper of some sort. For example, I used the following criteria to set up a true/false value in column B:

- Cell value is 8 characters long
- Cell value starts with "DN"
- Third character of cell value is either "X" or a number
- Rest of cell value is numbers

Here's the formula:

Book1
	A	B
1	DN920134	TRUE
2	DNX12345	TRUE
3	DNG08762	FALSE
Sheet1

Cell Formulas
Range	Formula
B1	=AND(LEN(A1)=8,LEFT(A1,2)="DN",OR(MID(A1,3,1)="X",ISNUMBER(FIND(MID(A1,3,1),"0123456789"))),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,4,5),0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))=0)

It's quite large

I then added data validation on A1 using "Custom" and a formula of =B1=TRUE

It worked but it's clunky and perhaps there's a better way.

WBD

 

[Wyvern]

Maybe that help you formulate what you need: https://www.ablebits.com/office-add...-data-validation-excel-custom-rules-formulas/[/QUOTE]

Thank you for these! Answer some of my other challenges You're a STAR

 

[Wyvern]

Thanks for the suggestion - Unfortunately this is going to be used by a number of different people and I want to create it so it will only accept the reference in a strick format