Hide sheets via autofilter?

Hi guys and gals,

Here's my situation. I have a workbook w/ 120+ sheets, and the first one is a table of contents, while each following sheet is a record sheet for one of 10 individuals. That is, sheets 2-8 are for Alice, 9-12 for Bob, 13-29 for Charles, etc. Individual names are in column C, sheet names are hyperlinked text in column A on the table of contents sheet.

I want to know how to hide all the sheets which are not meant for the individual based on the autofilter of Column C. If I select Alice as the autofilter criteria for Column C, I want all the sheets except the table of contents and her sheets 2-8 to be hidden.

I'm pretty sure I need to use the .Criteria1 and .On properties, but I'm not sure how. Any insights or suggestions would be, as always, appreciated.

Thanks,
Adam

Um,Why?
Why would you use AutoFilter to do this?
What are you trying to achieve by doing this?

Originally Posted by davesexcel

Um,Why?
Why would you use AutoFilter to do this?
What are you trying to achieve by doing this?

davesexcel,

I'm using AutoFilter because it's the best/easiest way I know to filter a big table of stuff based on one column. Is there a better method you can suggest?

-Adam

Originally Posted by ahartman

davesexcel,

I'm using AutoFilter because it's the best/easiest way I know to filter a big table of stuff based on one column. Is there a better method you can suggest?

-Adam

Maybe there is, lets see a sample workbook.

I composed the file whilst davesexcel replied so apologies if encroaching... just struck me as an interesting idea.

Again, as usual, I am not entirely sure I follow the requirements but perhaps the attached may be of interest ?

(the attached is also using the old XL4 GET.WORKBOOK calls via Names to generate the sheet listings / hyperlinks etc...)

Originally Posted by davesexcel

Maybe there is, lets see a sample workbook.

davesexcel,

Attached is a sample for your confusion

-Adam

Originally Posted by DonkeyOte

I composed the file whilst davesexcel replied so apologies if encroaching... just struck me as an interesting idea.

Again, as usual, I am not entirely sure I follow the requirements but perhaps the attached may be of interest ?

(the attached is also using the old XL4 GET.WORKBOOK calls via Names to generate the sheet listings / hyperlinks etc...)

DonkeyOte,

Yeah, this does what I wanted exactly. What are the potential pitfalls of relying on the XL4 GET.WORKBOOK calls? Also, are you programatically generating the hyperlinks? I didn't see that code anywhere...

-Adam

EDIT: Actually, after looking more closely, I see that if I filter for Donkey1 in column A, all sheets but Donkey1 are hidden, which is the expected outcome. However, if I then change the filter to Donkey2, still only the Donkey1 sheet is visible. I don't really need the functionality of each filter column hiding sheets, just one column in particular. It may be easier to just do them all, though, since your implementation of the idea seems pretty clean and concise. I'll dissect the code more and see what sense I can make of it.

In reality it's down to the use of the Static Variable... so it could be modified (I was considering only use of Names rather than Sheet Names but the behaviour you outline was not accounted for).

In essence altering a Filter Selection does not invoke the Change event of the sheet (ie of the cell in which the filter resides). What it will invoke (if sheet is set up appropriately) is the Calculate event. However, the Calculate event could itself be invoked for a number of reasons other than a filter being set... and given the nature of the operation at hand it is in reality in your interests to limit the number of times your sheet hide/unhide operation is put into practice. On that basis I used a Static variable approach where the Static variable is designed to hold the name "before" any potential change to the filter is made... this value is subsequently compared to the current value at time of Calculation and only if those two values are different would you conduct your worksheet operation (and update the value of the Static variable accordingly)... thus for any other action generating a recalculation the sheet visibility routine itself would not be executed given the Static variable and current filter value would be unchanged.

So ... if the above mumblings made sense it follows the static variable could be set to contain a concatenation of both name and sheet and compared to the concatenation of current values... where different the routine needs to be invoked - thus if either sheet name or individual name is altered the code should react accordingly.

As a follow-up to above, perhaps then:

Please Login or Register  to view this content.

Not also an added test was added to determine as to whether or not any filter is being applied in the first instance.

Originally Posted by DonkeyOte

As a follow-up to above, perhaps then:

Please Login or Register  to view this content.

Not also an added test was added to determine as to whether or not any filter is being applied in the first instance.

DonkeyOte,

There was actually a slight bug in your second set of code. .FilterMode expects a worksheet as a parent. I changed it in blue to be ActiveSheet. This works just as I'd hoped. What's the target area to focus on if I want to filter against column C, and if the sheet names aren't variations of the values in column C? i.e, if the column C value is Alice and the sheets for Alice are Chart 1, Chart 2, Chart 3, etc?

Thanks,
Adam

Originally Posted by ahartman

There was actually a slight bug in your second set of code. .FilterMode expects a worksheet as a parent. I changed it in blue to be ActiveSheet.

Interesting... the same would however work without issue in XL2007 (filtering is slightly different)

Originally Posted by ahartman

What's the target area to focus on if I want to filter against column C, and if the sheet names aren't variations of the values in column C? i.e, if the column C value is Alice and the sheets for Alice are Chart 1, Chart 2, Chart 3, etc?

The name of the associated sheets is not really important (the code merely cycles the visible cells and unhides as appropriate) ... what is important is that you're referring now to Chart Objects in addition to (or replacement of) Worksheet objects.
On that basis the code would need to be altered such that instead of using:

Please Login or Register  to view this content.

you would declare as an Object... now either you can change Worksheet to Object in above line or if you're like me (anally retentive) you may opt to rename your variable in accordance with the type such that the above is removed and replaced by:

Please Login or Register  to view this content.

regardless you would need to alter the iteration from processing the Worksheet objects in the active workbook to all the Sheet objects in the active workbook, ie from:

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

If you do opt to change to oSt from ws then you must change all references to ws to be oSt.

I prefer to use notation of sorts in my variables so I can determine type from their name (o for Object, v for Variant, dbl for Double etc... each to their own)

Actually, the sheets are just named Chart 1, Chart 2, etc. The meat of this workbook is outside of my influence, I'm merely bolting stuff onto a local copy that has to maintain surface level compatibility w/ the original. What drives your "as appropriate" distinction for unhiding sheets? To be honest, I've been discussing the code more than I've been examining it, so it may be obvious and I'm just in a "forest for the trees" mode right now.

-Adam

The main body of work is done in two parts, the first:

Please Login or Register  to view this content.

merely hides every sheet other than the TOC sheet

the second part, the bit you're interested in, is below:

Please Login or Register  to view this content.

the above, utilising the Filter Range, simply iterates every visible cell in Column 1 post-filter and uses the value of that cell as a sheet name - that sheet is then made visible... thus if you're interested in contents of the third column in the filter range you can simply change Columns(1) to Columns(3)... ie column 3 contains the sheet names.
The last line is used to update the Static variable.

I have no doubt the above can be streamlined / improved upon (particular in relation to working with sheet arrays) but you should hopefully find it points you in the right direction nonetheless.

On an aside please don't quote posts in their entirety unless strictly necessary - it basically clutters up the board - use the Post Reply button or remove whichever parts of the quoted text are not particularly relevant to your next post.

Originally Posted by DonkeyOte

the above, utilising the Filter Range, simply iterates every visible cell in Column 1 post-filter and uses the value of that cell as a sheet name - that sheet is then made visible... thus if you're interested in contents of the third column in the filter range you can simply change Columns(1) to Columns(3)... ie column 3 contains the sheet names.
The last line is used to update the Static variable.

DonkeyOte,

Please Login or Register  to view this content.

Ok, after transplanting this code into my own sample book, it's not functional and I'm baffled. The Calculate event is never getting trapped by the subroutine. I threw in some msgboxes and commented out the screenupdating to just get a verification, and I never see any popups. If you've got any intuitions about that, I'd be happy to hear them, but don't spend time you don't have, you've already been more helpful than I should rightfully expect

First, check you're running in Auto Calc mode.

Assuming you are I'd refer back to a subtle (me?) point I made earlier on:

Originally Posted by DO

...a Filter Selection does not invoke the Change event of the sheet (ie of the cell in which the filter resides). What it will invoke (if sheet is set up appropriately) is the Calculate event.

The key part in the above is the last sentence and specifically the text within parentheses. In my example I'm using formulae in such a way that filtering will ensure a calculation is invoked... a quick way to be sure this will happen in your file is to add a Volatile function to the sheet against which this code is being applied, eg:

Z1: =NOW()

Given the act of altering row visibility is a Volatile one it follows that any cells containing Volatile functions will flag as dirty and in need of recalculation thereby ensuring that in Auto Mode at least the event will fire.

If unfamiliar with Volatility etc see the link in my sig. to Charles Williams' decisionmodels website - an amazing resource.

Originally Posted by DonkeyOte

The key part in the above is the last sentence and specifically the text within parentheses. In my example I'm using formulae in such a way that filtering will ensure a calculation is invoked... a quick way to be sure this will happen in your file is to add a Volatile function to the sheet against which this code is being applied, eg:

Z1: =NOW()

Given the act of altering row visibility is a Volatile one it follows that any cells containing Volatile functions will flag as dirty and in need of recalculation thereby ensuring that in Auto Mode at least the event will fire.

DonkeyOte,

This cleared it up. Thanks for your deep knowledge and advice. I'll take a closer look at your formulae in your original file and see how they'll fare in my live sheet. I originally made the hyperlinks via VBA, but your formula solution looked just as good. If that brings in volatility w/o needing to have a hidden =NOW() cell on the sheet, I might just go that route.

Again, many thanks!

-Adam