Macro to Hide and Unhide Rows

[SimonG]

I want to show (or not show) various rows in a sheet depending on a Yes/ No pull-down answer to a question. Would it be possible to have some clever macro that ran automatically once the answer to a yes/no question was entered into a cell, once yes was entered certain rows would be visible and others hidden and vice versa for a No answer?

 

[VoG]

Which cell contains the data validation and which rows should be visible or hidden?

 

[SimonG]

The Data Validation cell is B5,
if “Yes” then Hide Rows 7 to 11 and Show Rows 12 to 15;
if “No” then Show Rows 7 to 11 and Hide Rows 12 to 15;
if blank (neither “Yes” nor No) then Hide Rows 7 to 11 and Hide Rows 12 to 15.

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B5" Then
Select Case Target.Value
    Case "Yes": Rows("7:11").Hidden = True: Rows("12:15").Hidden = False
    Case "No": Rows("7:11").Hidden = False: Rows("12:15").Hidden = True
    Case Else: Rows("7:15").Hidden = True
End Select
End If
End Sub

 

[SimonG]

I though that I could figure this bit out, but allas No, what if the sheet is protected with password "a", so I need a:

.Unprotect Password:="a"

.Protect Password:="a"

in there somewhere?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B5" Then
    Me.Unprotect Password:="a"
    Select Case Target.Value
        Case "Yes": Rows("7:11").Hidden = True: Rows("12:15").Hidden = False
        Case "No": Rows("7:11").Hidden = False: Rows("12:15").Hidden = True
        Case Else: Rows("7:15").Hidden = True
    End Select
    Me.Protect Password:="a"
End If
End Sub

 

[SimonG]

Having sorted that out and got it working well how do I vary the number rows that I want to close based on the value of an integer in another cell, say, D5 =3 for example. So the rows would be hidden if the answer was “No”, but the rows 9 to 9+3 would be shown if “Yes” and 3 were entered?

 

[VoG]

Try like this

Case "Yes": Rows(7).Resize(Range("B5").Value).Hidden = True: Rows(12).Resize(Range("B5").Value).Hidden = False

 

[SimonG]

I have made a mistake somewhere along the line becuase the code, shown below, does not work.
I want to ask the 'Yes/ No' question in cell B5.
If No then all rows (7:11) are hidden.
If 'Yes' the depending on the value in cell D5 (1 to 5) the specify number of rows are shown 1=7; 2=7-8, 3=7-9, .... 5=7-11?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B5" Then
Me.Unprotect Password:="a"
Select Case Target.Value
Case "Yes": Rows(7).Resize(Range("D5").Value).Hidden = True: Rows(12).Resize(Range("D5").Value).Hidden = False
Case "No": Rows("7:11").Hidden = False: Rows("12:15").Hidden = True
End Select
Me.Protect Password:="a"
End If
End Sub

 

[VoG]

So shouldn't it be

Case "No": Rows("7:11").Hidden = True: Rows("12:15").Hidden = True