Hiding Row based on cell value

[mohd21uk via OfficeKB.com]

I would like to hide row C17 based on the value entered in cell C16. For e.g.
If the value C16 is yes, C17 should remain in the unhide format. However, if
no is entered in cell c16, row c17 should be hidden. This should be executed
everytime the value of c16 is changed.

Thanks

 

[MattShoreson]

have a worksheet change event look for the value in C16.

If yes then hide 17, conversely, do the opposite for no.

 

[Stefi]

This is Worksheet_Change event procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C16" Then
If Target.Value = "yes" Then Rows("17").EntireRow.Hidden = False
If Target.Value = "no" Then Rows("17").EntireRow.Hidden = True
End If
End Sub

Post if you need help to install it!

Regards,
Stefi

„mohd21uk via OfficeKB.com†ezt írta:

 

[Ivan Raiminius]

Hi,

paste this code into appropriate sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$16" Then
Select Case UCase(Target.Value)
Case "YES"
Rows(17).Hidden = False
Case "NO"
Rows(17).Hidden = True
end select
End If
End Sub

It does exactly what you describe in your post.

Regards,
Ivan

 

[Tom Ogilvy]

Here is an additional method:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0,0) = "C16" Then
Rows(17).Hidden = (lcase(target) = "no")
End If
End Sub

Right click on the sheet tab of this worksheet and select view code.
in the resulting code module, from dropdown at the top left of the module,
select Worksheet and from the right select Change (not selectionchange).
This will put the procedure declaration in the module. It is always best to
enter an event this way. then modify it to appear as above.

This will fire whenever a cell is edited, but the code only takes action
when the trigger cell is C16 (a reference to the trigger cell is contained in
the Target parameter of the event).

An overview of events can be found at Chip Pearson's site
http://www.cpearson.com/excel/events.htm

 

[TEAM]

I have a workbook with several sheets containing data organized in rows.
The sheets contain the ALL possible information that I would want to
see. But, most of the time I only want to see select rows.

I have tried to think of the best way to do this, and I am trying to
set it up as follows...

Menu page - contains about 5 combo boxes with "yes" or "no" in each.
Each box would be used to evaluate what range of data I want to see.

All other sheets - have a dedicated column (AA) that links to the
yes/no questions.

If on the first combo box I select "yes", then it will look for all
rows in column AA of sheets 2, 3, and 4 and determine if they contain
the work "bird". If they do, then the row will remain. If I select
"no", then all rows containing "bird" will be deleted.

If on the second combo box I select "yes", then it will look for all
rows in column AA of sheets 2, 3, and 4 and determine if they contain
the work "dog". If they do, then the row will remain. If I select
"no", then all rows containing "dog" will be deleted.

...ETC.

The problem I see is that the original file should never be saved with
changes. After making selections in the 5 combo boxes, the user would
click submit and...

-all deletions would be made
-sheets 2, 3, and 4 would be saved in a new file
-original file closed without saving changes

 

[MattShoreson]

why not use autofilter instead? Then you wont need to worry about not
saving

 

[TEAM]

I did not explain the issue as well as I should have. The autofilter
would work for what I said, but the output really needs to be more
formal and finalized than an autofilter screen...

After the sheets are shortened to only the needed information, they
will be sent out as a report to users. I think that the process needs
to be automated such that all preferences are made on an initial "menu"
sheet, all changes are made upon clicking submit, and a final output is
generated.

...any ideas?


Thanks.