Conditional Hide/Unhide Rows
 

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.

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.

----
Regards,
John Mansfield
http://www.pdbook.com


"Gwen H" wrote:

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.

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


"Gwen H" <Gwen wrote in message
...
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.

Thanks! It works like magic.

"Trevor Shuttleworth" wrote:


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


"Gwen H" <Gwen wrote in message
...
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.

Your'e welcome. Thanks for the feedback.


"Gwen H" wrote in message
...
Thanks! It works like magic.

"Trevor Shuttleworth" wrote:


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


"Gwen H" <Gwen wrote in message
...
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.