Two sets of data validation on the same cells?

[slam]

Is this possible?

I have cells D2 to D17 set with data validation to only allow values between 1 and 16. I also want to prevent duplicate entries. Is there a way to do this?

Thanks

 

[T. Valko]

Is this possible?

I have cells D2 to D17 set with data validation to only allow values between 1 and 16. I also want to prevent duplicate entries. Is there a way to do this?

Thanks

Try this...

Select the range D2:D17
Data Validation
Allow: Custom
Formula:

=AND(MOD(D2,1)=0,D2>=1,D2<=16,COUNTIF(D$2:D$17,D2)<2)

OK out

 

[slam]

Try this...

Select the range D2:D17
Data Validation
Allow: Custom
Formula:

=AND(MOD(D2,1)=0,D2>=1,D2<=16,COUNTIF(D$2:D$17,D2)<2)

OK out

I am getting an error when I try to apply that. I think it might not be in agreement with some VB (that maybe you gave me?) to convert "1" into "1st" etc:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Formula
Case 1: Target.Value = Target.Value & "st"
Case 2: Target.Value = Target.Value & "nd"
Case 3: Target.Value = Target.Value & "rd"
Case 4 To 16: Target.Value = Target.Value & "th"
End Select
Application.EnableEvents = True

End Sub

 

[T. Valko]

I am getting an error when I try to apply that. I think it might not be in agreement with some VB (that maybe you gave me?) to convert "1" into "1st" etc:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Formula
Case 1: Target.Value = Target.Value & "st"
Case 2: Target.Value = Target.Value & "nd"
Case 3: Target.Value = Target.Value & "rd"
Case 4 To 16: Target.Value = Target.Value & "th"
End Select
Application.EnableEvents = True

End Sub

If you have that event macro then you'll want to tweak it to include the validation.

I'm not much of a programmer so someone else will need to help you with that.

 

[slam]

If you have that event macro then you'll want to tweak it to include the validation.

I'm not much of a programmer so someone else will need to help you with that.

Or could I modify the data validation to account for 1st through 16th as well?

 

[T. Valko]

Or could I modify the data validation to account for 1st through 16th as well?

The menu command data validation won't work if the range has an event macro entering the data.

The menu command data validation will only work for manually entered data.

 

[Marcelo Branco]

Maybe this can help

I'm just trying to insert the validation rules provided by T. Valko in the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("D2:D17")) Is Nothing Then
        If Target.Cells.Count > 1 Then Exit Sub
        If Target = "" Then Exit Sub
        
        Application.EnableEvents = False
        
        If Not IsNumeric(Target) Then GoTo HdlError
        Target = Int(Target)
        If Not ((Target >= 1) And (Target <= 16)) Then GoTo HdlError
        
        Select Case Target.Formula
            Case 1: Target.Value = Target.Value & "st"
            Case 2: Target.Value = Target.Value & "nd"
            Case 3: Target.Value = Target.Value & "rd"
            Case 4 To 16: Target.Value = Target.Value & "th"
        End Select
        
        If Application.CountIf(Range("D2:D17"), Target.Value) > 1 Then GoTo HdlError
        
        Application.EnableEvents = True
    End If
    
    Exit Sub
    
HdlError:
    MsgBox "Invalid or duplicate entry"
    Target = ""
    Target.Select
    Application.EnableEvents = True
End Sub

Hope this works

M.