Multiple DATA Validation for same cells

[kimjon76]

Hi,

I need support on how to do a multiple data validation entry in the same column (Same cells). I want to restrict the entries in B1 to B20 to only "X" and "B" in a drop down menue in each cell. (easy, just do a data validation and choose list. BUT, I also want to restrict the entries to maximum 10 X´s and 4 B´s in the cells B1 to B20. Is this possible at all?

Pls help.

br
Kim

 

[MickG]

Try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim str As String
Dim Col As Integer
If Not Intersect(Target, Range("B:B,C:C,D:D,E:E,F:F")) Is Nothing Then
Col = Target.Column
Set Rng = Range("A1:A20").Offset(, Col - 1)
    str = str & IIf(Application.CountIf(Rng, "X") >= 10, " ", ",X")
    str = str & IIf(Application.CountIf(Rng, "B") >= 4, " ", ",B")
    str = str & IIf(Application.CountIf(Rng, "T") >= 4, " ", ",T")
With Rng.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=str
End With
End If
End Sub

 

[kimjon76]

Hi again,

WOW! Worked perfectly as well. Thanks. I am impressed. So, final question. If I want to add another range of cells further down in the columns, but with the exact same preconditions. So also adding for instance cells in range 30 to 50 in column B, C, D, E, F that should perform the same as the already existing range B1-B20, and C1-C20, and so on. Is this possible?

br
Kim

Try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim str As String
Dim Col As Integer
If Not Intersect(Target, Range("B:B,C:C,D:D,E:E,F:F")) Is Nothing Then
Col = Target.Column
Set Rng = Range("A1:A20").Offset(, Col - 1)
    str = str & IIf(Application.CountIf(Rng, "X") >= 10, " ", ",X")
    str = str & IIf(Application.CountIf(Rng, "B") >= 4, " ", ",B")
    str = str & IIf(Application.CountIf(Rng, "T") >= 4, " ", ",T")
With Rng.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=str
End With
End If
End Sub

 

[MickG]

You would need to change the line in red, for same columns, but with ranges Rows 1 to 20 and Rows 30 to 50.

Col = Target.Column
Set Rng = IIf(Target.Row <= 20, Range("A1:A20").Offset(, Col - 1), Range("A30:A50").Offset(, Col - 1))
    str = str & IIf(Application.CountIf(Rng, "X") >= 10, " ", ",X")

 

[kimjon76]

Hi again, wow, thank you so much. Sorry for coming back again and again, but this is the final question. If I want to continue to add cells in range lower down in the document, that is behaving the same. (for instance B50 to B70 (and C,D,E,F) and B90 to B110 (and C,D,E,F), how do I do this?

 

[MickG]

Try this:-
NB:- See "Remarks in 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
[COLOR="Navy"]Dim[/COLOR] str         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] mRng        [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] R           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("B:B,C:C,D:D,E:E,F:F")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
Col = Target.Column
'[COLOR="Green"][B]@@@@@@@ Add your new Addresses as below @@@@@@[/B][/COLOR]
mRng = Array(Range("A1:A20"), Range("A30:A50"), Range("A60:A80"))
[COLOR="Navy"]For[/COLOR] R = 0 To UBound(mRng)
    [COLOR="Navy"]If[/COLOR] Not Intersect(Target, mRng(R).EntireRow) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Set[/COLOR] Rng = mRng(R).Offset(, Col - 1)
    str = str & IIf(Application.CountIf(Rng, "X") >= 10, " ", ",X")
    str = str & IIf(Application.CountIf(Rng, "B") >= 4, " ", ",B")
    str = str & IIf(Application.CountIf(Rng, "T") >= 4, " ", ",T")
[COLOR="Navy"]With[/COLOR] Rng.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=str
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[pjmorris]

Try this - its somewhat simpler (honest) than using code:

1. Click and drag from cell B1 to F20. (Its important to do it this way as the formula is based on the starting cell (the Active cell))
2. Select Data Validation from the Data Ribbon.
3. In the Allow criteria select Custom.
4. In the formula cell enter: =AND(OR(B1="X",B1="B",B1="T"), COUNTIF(B$1:B$20,"X") <= 4, COUNTIF(B$1:B$20,"B") <= 10, COUNTIF(B$1:B$20,"T") <= 4) dont worry about amending the cell ranges, excel will sort this out automatically.
5. Click OK.

Hope this helps.

Regards

 

[saifee2]

Hello,

I need to do multiple validations in a cell. Character length doesn’t exceed 40 character limit and cell doesn’t take special characters like ‘,’ and ‘#’</SPAN></SPAN>. thanks in advance.