hide or unhide worksheets based on cells in another sheet

[miconian]

Hi gurus,

Let's say that I have one worksheet that is never hidden, and on that worksheet I have a column called "fruit." Then I have three hidden worksheets: the apple worksheet, the pear worksheet, and the orange worksheet.

Well, if "apple" occurs anywhere in the fruit column on the main worksheet, then I want the apple worksheet to be visible. If not, then I want it hidden. Ditto for pear and orange.

What think you?

 

[miconian]

Re: hide or unhide worksheets based on cells in another shee

...hm. On further testing, doesn't work as well as I thought. I guess the worksheet doesn't calculate after a mere change in text in a single cell.

 

[miconian]

Re: hide or unhide worksheets based on cells in another shee

I've got it working ok by using both worksheet_activate and worksheet_change.

A lingering question: What if I want to check more than one column? Can I say something equivalent to

With Columns ("A:A") and ("R:R")

?

 

[miconian]

Re: hide or unhide worksheets based on cells in another shee

To clarify: The issue is that there is one worksheet which actually corresponds to about nine different possible entries in column A. So, you have helped me to say "If column A contains 'apples,' then show the apple worksheet. If column A contains 'pears,' then show the pear worksheet."
Now, I want to add:
"If column A contains 'granite,' limestone,' 'coal,' or 'quartz,' then show the rock worksheet."

Two factors that may or may not make this task easier. One: column R now shows 'rock' on every row in which column A shows a particular rock. Two: There is a range on 'Sheet 2' listing all the rocks.

 

[Richie(UK)]

Re: hide or unhide worksheets based on cells in another shee

For reference, see also:

http://www.vbaexpress.com/forum/showthread.php?p=8263#post8263