Using vba to hide rows from multiple data validation drop down boxes

Question

Hi, I'm trying to figure out how to get a macro to hide a row in excel if "No" is selected in two different drop down boxes.

I had a go at using the code below to hide a row based on a single drop down, and figured I'd put an if function in the trigger cell that says "No" if both the drop down cells say "No" also, effectively allowing me to only hide the row if both dropdowns are satisfied. But this isn't working.

Am I doing something wrong, or is there perhaps an easier way to hide a row only when two different dropdown boxes have the same response?

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

Set Triggercell = Range("E5")

If Not Application.Intersect(Triggercell, Target) Is Nothing Then
If Triggercell.Value = "No" Then
Rows("6").Hidden = True
ElseIf Triggercell.Value = "Yes" Then
Rows("6").Hidden = False
ElseIf Triggercell.Value = "" Then
Rows("6").Hidden = False
End If
End If
End Sub

riny_van_eekelen · Accepted Answer

KyHeffernan&nbsp;This code will achieve what you want. In this example your two triggers are in E5 and F5.Private Sub Worksheet_Change(ByVal Target As Range)

Dim TriggerCell1, TriggerCell2 As Range
Set TriggerCell1 = Range("E5")
Set TriggerCell2 = Range("F5")

If TriggerCell1.Value = "No" And TriggerCell2.Value = "No" Then
        Rows("6").Hidden = True
        Else
        Rows("6").Hidden = False
    End If

End Sub&nbsp;

Forum Discussion

Using vba to hide rows from multiple data validation drop down boxes

2 Replies

Resources