Conditional Hide/Unhide Rows

[Guest]

I am working with a workbook that has one worksheet only. I want all rows
visible when the user opens the file. However, In cell B10, I have a
drop-down list I'm generating with data validation. The only values are YES
or NO, and YES is initially selected. If the user selects YES, then I want
the entire worksheet to remain visible. If the user selects NO, then I want
to hide rows 11 through 50, and display an error message entered in row 51.

Using previous posts, I've begun the code, but I know it's missing some
things.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" And Target.Value = "YES" Then
Range("B11").Select
ElseIf Target.Address = "$B$10" And Target.Value = "NO" Then
Rows("11:50").Select
Selection.EntireRow.Hidden = True
Range("A51").Select
End If
End Sub

Any help you can give me would be greatly appreciated.

 

[Guest]

Gwen, try . . .

Sub HideRows()
Dim Rng As Range
Set Rng = Sheets("Sheet1").Range("B10")
If Rng.Value = "YES" Then
Rows("11:50").EntireRow.Hidden = False
Range("B11").Select
ElseIf Rng.Value = "NO" Then
Rows("11:50").EntireRow.Hidden = True
Range("B51").Select
MsgBox "Your Error Message"
End If
End Sub

You can tie this macro to the drop-down list control. If using the
drop-down from the forms toolbar, right-click on the control and go to the
Assign Macro option.

 

[Trevor Shuttleworth]

Gwen

one way:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$B$10" And UCase(.Value) = "YES" Then
Rows("11:50").EntireRow.Hidden = False
Range("B11").Select
ElseIf .Address = "$B$10" And UCase(.Value) = "NO" Then
Rows("11:50").EntireRow.Hidden = True
Range("A51").Select
End If
End With
End Sub

Regards

Trevor

 

[Guest]

Thanks! It works like magic.

Gwen

one way:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$B$10" And UCase(.Value) = "YES" Then
Rows("11:50").EntireRow.Hidden = False
Range("B11").Select
ElseIf .Address = "$B$10" And UCase(.Value) = "NO" Then
Rows("11:50").EntireRow.Hidden = True
Range("A51").Select
End If
End With
End Sub

Regards

Trevor

 

[Trevor Shuttleworth]

Your'e welcome. Thanks for the feedback.

Thanks! It works like magic.