Conditional Hide/Unhide Rows

[Gwen H]

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.

[John Mansfield]

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.

[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


"Gwen H" <Gwen [email protected]> wrote in message
news:[email protected]...
>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 H]

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 [email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

[Trevor Shuttleworth]

Your'e welcome. Thanks for the feedback.


"Gwen H" <[email protected]> wrote in message
news:[email protected]...
> 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 [email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>