Hide/Unhide Rows Based on Various Drop Downs

user_15169569 0
Viewed 130

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
drop-down-menu hide vba excel
3 Answers

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 = "No"
        Rows("36:52").EntireRow.Hidden = .Value = "Yes"
    End If
End With
user_4788840

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
user_98140690

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
user_188247270
Related