Prev: How to Combine Columns of Text - sounds easy
Next: Hiding Rows Using IF Statement
From: Larry Fitch on 4 Apr 2010 13:19
I would like to be able to hide a number of rows based on the response to a
question..

The response to the question is a list box with either "yes" or "no" as the
choices.. If the response = "no" then I would like to hide the next 3 rows,
otherwise do nothing..

Is there a "hide" command of some sort that I can use as part of an IF
statement or is this going to require a macro ?


--
Thanks

Larry
From: JLatham on 4 Apr 2010 14:04
Requires a macro, specifically a Worksheet_Change() event macro:
Assuming your "YES/NO" choice is in column A the code below will work
unchanged, otherwise edit as needed after copying.

To put it to work in your workbook (make a copy to test with)
Open the test copy, select the sheet this needs to work with and then
right-click on its name tab and choose [View Code] from the list. Copy the
code below and paste it into the module presented to you. Close the VB
Editor and give it a test run.

Private Sub Worksheet_Change(ByVal Target As Range)
'change as required
Const colWithYesNoEntry = "A"
Dim CO As Integer

If Target.Cells.Count = 1 And Target.Column = _
Range(colWithYesNoEntry & "1").Column Then
' single cell changed in proper column
Application.ScreenUpdating = False
If UCase(Trim(Target)) = "YES" Then
'is YES, unhide next 3 rows
For CO = 1 To 3
Target.Offset(CO, _
0).EntireRow.Hidden = False
Next
'go down to next row
Target.Offset(1, 0).Activate
ElseIf UCase(Trim(Target)) = "NO" Then
'assumed to be NO
'hide next 3 rows
For CO = 1 To 3
Target.Offset(CO, _
0).EntireRow.Hidden = True
Next
'and move down below the 3 hidden rows
Target.Offset(4, 0).Activate
End If
End If
End Sub




"Larry Fitch" wrote:

> I would like to be able to hide a number of rows based on the response to a
> question..
>
> The response to the question is a list box with either "yes" or "no" as the
> choices.. If the response = "no" then I would like to hide the next 3 rows,
> otherwise do nothing..
>
> Is there a "hide" command of some sort that I can use as part of an IF
> statement or is this going to require a macro ?
>
>
> --
> Thanks
>
> Larry
 | 
Pages: 1
Prev: How to Combine Columns of Text - sounds easy
Next: Hiding Rows Using IF Statement