Yes/No vs. No/Yes

[u_bull]

Hi,

A product can either be for surface use or for subsea use - not both at the same time...

Surface	Yes
Subsea	No

<tbody>
</tbody>

I have made Yes/No selectable from a dropdown.
But...

These two fields (B1 vs B2) must be of opposite values...

Ie.
If I select B1 as Yes, then B2 should change to No - and vice versa.
If I select B2 as Yes, then B1 should change to Yes - and vice versa.

I can't seem to avoid a circular reference - my VBA stops...

The below works fine for one field... But... :-/

Any ideas?

Thanks...



Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("b2:b2")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

If Worksheets(1).Range("B2").Value = "Yes" Then Worksheets(1).Range("B1").Value = "No"
If Worksheets(1).Range("B2").Value = "No" Then Worksheets(1).Range("B1").Value = "Yes"

End If
End Sub

 

[Macropod]

The simplest approach is to use a single dropdown with three options - null, Surface, Subsea. That way, you know whether the user has made a valid choice - only one of which can be made.

 

[NARAYANK991]

Hi ,

Try this :

Private Sub Worksheet_Change(ByVal Target As Range)
            Dim KeyCells As Range, Destination As Range
            Dim TheOther As String
            
            Set KeyCells = Range("B1:B2")


            If Not Application.Intersect(Target, KeyCells) Is Nothing Then
               If Target = "Yes" Then TheOther = "No" Else TheOther = "Yes"
               If Target.Address = "$B$1" Then
                  Set Destination = [B2]
               Else
                  Set Destination = [B1]
               End If
               
               Application.EnableEvents = False
               Destination = TheOther
               Application.EnableEvents = True
            End If
End Sub

 

[u_bull]

Hi ,

Try this :

Private Sub Worksheet_Change(ByVal Target As Range)
            Dim KeyCells As Range, Destination As Range
            Dim TheOther As String
            
            Set KeyCells = Range("B1:B2")


            If Not Application.Intersect(Target, KeyCells) Is Nothing Then
               If Target = "Yes" Then TheOther = "No" Else TheOther = "Yes"
               If Target.Address = "$B$1" Then
                  Set Destination = [B2]
               Else
                  Set Destination = [B1]
               End If
               
               Application.EnableEvents = False
               Destination = TheOther
               Application.EnableEvents = True
            End If
End Sub

Ha ha - great. That works!!
Brilliant...

Also thank you to Macropod - but the question is taken out of context and I can't simply ask if it surface or subsea.
I have​ to ask two questions...

 

[Peter_SSs]

Ha ha - great. That works!!

a) I'm not sure if it is likely to happen with your sheet but if you decided to clear both B1 & B2 at the same time by selecting them and pressing the Delete key, the suggested code will error.
b) If the cells have been filled in with a Yes and a No and you clear the Yes cell, the other cell will change from No to Yes. That may be undesirable?

The alternative code below addresses both of the above points.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim KeyCells As Range
  
  If Target.Cells.Count = 1 And Target.Cells(1).Value <> "" Then
    Set KeyCells = Range("B1:B2")
    If Not Intersect(Target, KeyCells) Is Nothing Then
      Application.EnableEvents = False
      KeyCells.Cells(2 - Target.Row + KeyCells.Row).Value = Replace("YesNo", Target.Value, "")
      Application.EnableEvents = True
    End If
  End If
End Sub