Using data validation to control text length w/Vlookup

[Questionasker]

Can anyone provide a formula to be used in data validation that will control text length (6 digits) and restrict duplicate entries. The best formula will prevent anything other that 6 digits, but question the user regarding a duplicate entry.
For example: if the user enters 123456 no problem, but if 12345 is entered, Excel validation would not allow. If the user enters 123456 again, Excel's validation window would allow but the window will pop-up and ask to confirm.

Thank you,
Jon

 

[Questionasker]

Hello Peter,

Thanks for the reply. I removed all data validation, checked the security settings (was set to low, now medium) and added in the immediate window "Application.EnableEvents=True and press Enter".

Regarding the immediate window, was "and press Enter" part of the code?
It seems you were telling me to type the code and then press enter. I did that and all that happened was the cursur dropped down to the next line. Sorry for my lack of understanding code.

Following your instructions, I got out and re-opened the file, when I try an incorrect input, ex. 5 digits, not 6, I get a runtime error 1004 (Cannot chage part of a merged cell).

The range is "E14:E33" (formally F14:F33, code has been updated) containing merged cells. I unmerged one of the cells and it seemed to work. The merged range is "E14:H33", e.g. the first cell (merged) in the range is: E14:H14. Also, the values are manully typed and are not part of a formula.

Below is the current code pasted into the sheet tab. When the error occurs, I click the debug and the VBA window has .ClearContents highlighted in yellow after If anError Then
With Target (shown in code below in bold italics).

Please provide your thoughts on the problem, I think we are very close.

Thanks again,
Jon

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Dim myVal As Variant
    Dim anError As Boolean, aWarning As Boolean
    Dim Resp As VbMsgBoxResult
    
    If Target.Count > 1 Then Exit Sub
    Set myRange = Range("E14:E33")
    If Not Intersect(Target, myRange) Is Nothing Then
        Application.EnableEvents = False
        myVal = Target.Value
        
        If IsNumeric(myVal) Then
            If Int(myVal) <> myVal Or myVal > 999999 Or myVal < 100000 Then
                anError = True
            Else
                If WorksheetFunction.CountIf(myRange, myVal) > 1 Then
                    aWarning = True
                End If
            End If
        Else
            anError = True
        End If
        
        If anError Then
            With Target
                [I][B].ClearContents[/B][/I]
                .Select
            End With
            MsgBox myVal & " is an invalid entry." & vbLf & "Enter a 6 digit number."
        End If
        
        If aWarning Then
            Resp = MsgBox(myVal & " is a duplicate, sure you want to keep it?", vbYesNo)
            If Resp = vbNo Then
                With Target
                    .ClearContents
                    .Select
                End With
            End If
        End If
        
        Application.EnableEvents = True
    End If
End Sub

 

[Peter_SSs]

... was "and press Enter" part of the code?

No

It seems you were telling me to type the code and then press enter. I did that and all that happened was the cursur dropped down to the next line.

Yes, that is what I was telling you and that is exactly all that you should see happen (cursor just moves to the next line)

... I get a runtime error 1004 (Cannot chage part of a merged cell).

The range is "E14:E33" (formally F14:F33, code has been updated) containing merged cells.

Ah, no mention previously of merged cells!

Coders generally don't much like merged cells - see here


Towards the bottom of the code there are two instances of

With Target

Change each of these to

With Target.Resize(, 4)

since your merged cells are 4 columns wide.

You may have to do the Application.EnableEvents=True bit in the Immediate window again.

See how that goes.

 

[Questionasker]

Peter,

Again, sorry for the delayed reply, I have been too busy to work on the sheet until this morning. Thank you very much, it seems to work like a charm. I appreciate your great help.

Thank you,
Jon