Hide/Unhide Rows Based on Various Drop Downs-CodePudding

I am trying to write a code that will hide/unhide rows based on drop down values. I can get the first drop down LLknownunknown to work just fine, but all the other drop downs don't work. Each drop down is named correctly and each row number is correct, so I am now sure what is going on.

So when LLknownunknown changes, the correct rows will hide/unhide. When KnownUnknown or Zeroknownunknown change, nothing happens. Each one will need to work independently.

Any thoughts? I triple checked and the cell names are correct.

Private Sub Worksheet_Change(ByVal Target As Range)

'The follow code will hide and unhide the Load Loss cell based on the user selection
    If Intersect(Target, Range("LLknownunknown")) Is Nothing Or Target.Cells.Count > 1 Then
        Exit Sub
        
    ElseIf Range("LLknownunknown").Value = "Yes" Then
        Rows("14:15").EntireRow.Hidden = False
        
    ElseIf Range("LLknownunknown").Value = "No" Then
        Rows("14:15").EntireRow.Hidden = True
        
    End If

'The follow code will hide and unhide the impedance cells based on the user selection
    If Intersect(Target, Range("KnownUnknown")) Is Nothing Or Target.Cells.Count > 1 Then
        Exit Sub
        
    ElseIf Range("KnownUnknown").Value = "Yes" Then
        Rows("21:30").EntireRow.Hidden = False
        Rows("36:52").EntireRow.Hidden = True

    ElseIf Range("KnownUnknown").Value = "No" Then
        Rows("21:30").EntireRow.Hidden = True
        Rows("36:52").EntireRow.Hidden = False
        
    End If
    
'The follow code will hide and unhide the zero sequence cells based on the user selection
    If Intersect(Target, Range("Zeroknownunknown")) Is Nothing Or Target.Cells.Count > 1 Then
        Exit Sub
        
    ElseIf Range("Zeroknownunknown").Value = "Yes" Then
        Rows("31:35").EntireRow.Hidden = False
        Rows("53:61").EntireRow.Hidden = True
        
    ElseIf Range("Zeroknownunknown").Value = "No" Then
        Rows("31:35").EntireRow.Hidden = True
        Rows("53:61").EntireRow.Hidden = False
        
    End If

End Sub

CodePudding user response：

If your ranges can only be "Yes" or "No", then you can simplify each block a little:

With Me.Range("KnownUnknown")
    If Not Intersect(Target, .Cells(1)) Is Nothing Then
        Rows("21:30").EntireRow.Hidden = .Value <> "Yes"
        Rows("36:52").EntireRow.Hidden = .Value = "Yes"
    End If
End With

CodePudding user response：

It's a logic error: when your range IS KnownUnknown or IS Zeroknownunknown then the very first IF test -

If Intersect(Target, Range("LLknownunknown")) Is Nothing

will return TRUE and exit the sub, so none of the rest of the code is even parsed.

Your exit condition should be something like

If (Intersect(Target, Range("LLknownunknown")) Is Nothing AND Intersect(Target, Range("KnownUnknown")) Is Nothing AND Intersect(Target, Range("Zeroknownunknownn")) Is Nothing) or Target.Cells.Count > 1 Then

CodePudding user response：

A Worksheet Change: Hide or Unhide Rows

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim iCell As Range
    
    ' Hide or unhide the load loss cells.
    Set iCell = Intersect(Target, Range("LLknownunknown"))
    If Not iCell Is Nothing Then
        Select Case LCase(CStr(iCell.Value))
        Case "yes"
            Rows("14:15").Hidden = False
        Case "no"
            Rows("14:15").Hidden = True
        'Case Else
        End Select
        Set iCell = Nothing
    End If

    ' Hide or unhide the impedance cells.
    Set iCell = Intersect(Target, Range("KnownUnknown"))
    If Not iCell Is Nothing Then
        Select Case LCase(CStr(iCell.Value))
        Case "yes"
            Rows("21:30").Hidden = False
            Rows("36:52").Hidden = True
        Case "no"
            Rows("21:30").Hidden = True
            Rows("36:52").Hidden = False
        'Case Else
        End Select
        Set iCell = Nothing
    End If
    
    ' Hide or unhide the zero sequence cells.
    Set iCell = Intersect(Target, Range("Zeroknownunknown"))
    If Not iCell Is Nothing Then
        Select Case LCase(CStr(iCell.Value))
        Case "yes"
            Rows("31:35").Hidden = False
            Rows("53:61").Hidden = True
        Case "no"
            Rows("31:35").Hidden = True
            Rows("53:61").Hidden = False
        'Case Else
        End Select
        Set iCell = Nothing
    End If

End Sub