If statement with two target address with multiple outcomes-CodePudding

I'm trying to build a code where the depending on the user input, it would hide certain rows. As an example, if the user selected "Yes", it would display certain rows and hide other rows. And the same when a user selects the answer "No". I was successfully able to create a this code. However, I have a similar situation, but there are two variables with the answer "yes" and "No". This would create 4 possible outcomes. I'm running into the issue that the code will not execute when the variables in B6 and B7 are modified.

I've included my line codes below. Could someone provide me with some advice as to how I can fix this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "B6:B7" Then
        If Range("B6").Value = "Yes" And Range("B7").Value = "Yes" Then
            Rows("12:38").EntireRow.Hidden = False
            Rows("39:57").EntireRow.Hidden = True
        ElseIf Range("B6").Value = "No" And Range("B7").Value = "Yes" Then
            Rows("12:38").EntireRow.Hidden = True
            Rows("39:57").EntireRow.Hidden = False
        ElseIf Range("B6").Value = "Yes" And Range("B7").Value = "No" Then
            Rows("12:38").EntireRow.Hidden = True
            Rows("39:57").EntireRow.Hidden = False
        ElseIf Range("B6").Value = "No" And Range("B7").Value = "No" Then
            Rows("11:38").EntireRow.Hidden = True
            Rows("39:57").EntireRow.Hidden = False
        End If
    End If
End Sub

CodePudding user response：

You can use Application.Intersect to check if any of the modified cells were in B6 or B7.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intersection As Range
    Set intersection = Application.Intersect(Range("B6:B7"), Target)
    If Not intersection Is Nothing Then
        If Range("B6").Value = "Yes" And Range("B7").Value = "Yes" Then
            Rows("12:38").EntireRow.Hidden = False
            Rows("39:57").EntireRow.Hidden = True
        ElseIf Range("B6").Value = "No" And Range("B7").Value = "Yes" Then
            Rows("12:38").EntireRow.Hidden = True
            Rows("39:57").EntireRow.Hidden = False
        ElseIf Range("B6").Value = "Yes" And Range("B7").Value = "No" Then
            Rows("12:38").EntireRow.Hidden = True
            Rows("39:57").EntireRow.Hidden = False
        ElseIf Range("B6").Value = "No" And Range("B7").Value = "No" Then
            Rows("11:38").EntireRow.Hidden = True
            Rows("39:57").EntireRow.Hidden = False
        End If
    End If
End Sub

CodePudding user response：

Your code will only execute when the modified range is exactly B6:B7.

It's always a good idea to use Intersect in these events because you don't know what is being passed in. It's also a good idea to separate the concerns for readability. Events should really just be reacting to something and then pass the work off to something else.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B6:B7")) Is Nothing Then SomeModule.ArrangeColumns
End Sub

'SomeModule
Sub ArrangeColumns()
    Dim b6 As Boolean: b6 = Range("B6").Value = "Yes"
    Dim b7 As Boolean: b7 = Range("B7").Value = "Yes"

'   Section 1 starts at row 11 or 12 if No, No.
    Dim sect1 As Range
    Set sect1 = Iif(not (b6 or b7), Rows("11:38"), Rows("12:38"))

    Dim sect2 As Range
    Set sect2 = Rows("39:57")

'   Toggle the section Hidden property based on input.
    sect1.Hidden = Not (b6 And b7)
    sect2.Hidden = b6 And b7
End Sub

Note that if you go from No Yes or Yes No to No No then row 11 still be hidden (unsure if that's intended).