Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hisind rows with an if statement
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hisind rows with an if statement
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rows vb statement | Excel Discussion (Misc queries) | |||
hiding rows with an if statement | Excel Worksheet Functions | |||
if statement using rows and columns | Excel Discussion (Misc queries) | |||
IF statement inserting new rows | Excel Programming | |||
Deleting Rows in Case Statement | Excel Programming |