VBA and dropdown list: a case of "only choice"

[Nelson78]

Hello everybody.

I've set a dropdown list as follows:

https://imgur.com/a/ntDkIK9

Dim MyListB(2) As String
MyListB(0) = "YES"
MyListB(1) = "NO"

Dim lrut2 As Long
    lrut2 = Cells(Rows.Count, "A").End(xlUp).Row

With Range("B2:B" & lrut2).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyListB, ",")
End With

For each record in column A, the user can select YES or NO.

The complication is:

some of the choices are not possible, meaning that if one record in column A is also present in column J, the only choice in column B is NO, so I have to set it in advance not allowing the user any action.

So, in this case, the value cells in B3, B4, B8 and B11 has to be "NO".

How could I manage this scenario?

 

[Nelson78]

Hello everybody.

I've set a dropdown list as follows:

https://imgur.com/a/ntDkIK9

Dim MyListB(2) As String
MyListB(0) = "YES"
MyListB(1) = "NO"

Dim lrut2 As Long
    lrut2 = Cells(Rows.Count, "A").End(xlUp).Row

With Range("B2:B" & lrut2).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyListB, ",")
End With

For each record in column A, the user can select YES or NO.

The complication is:

some of the choices are not possible, meaning that if one record in column A is also present in column J, the only choice in column B is NO, so I have to set it in advance not allowing the user any action.

So, in this case, the value cells in B3, B4, B8 and B11 has to be "NO".

How could I manage this scenario?

Any ideas?

 

[MickG]

Try this "Change Event" code:-
Paste code in Activesheet code module

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
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Not Target.SpecialCells(xlCellTypeAllValidation) [COLOR="Navy"]Is[/COLOR] Nothing And Target.Column = 2 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("J2", Range("J" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Target.Offset(, -1).Value = Dn.Value [COLOR="Navy"]Then[/COLOR]
            Target.Value = "No"
            [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick