I am fighting a quixotic battle against a problem, that I realise may well be unsolvable. I have a worksheet that consists of approximately 27,000 rows in a four-level structure, like so:
To make the sheet easier to navigate I have created an expanding/collapsing tree structure by using simple hide and unhide rows, and on the last level of headings (1.1.1) I have cells that change between "+" and "-" depending on whether the fourth-level subset is currently visible or hidden. Naturally I need to keep the cell as "+" when the set is collapsed, and "-" when expanded. I have accounted for all methods user can change the hidden settings of those rows, but one:
When users autofilter the list, the +/- signs obviously screw up, because filtering resets the user-set hidden-settings. Suddenly I have expanded subsets with "-" on headings that were previously "+".
Now this is obviously a vanity problem, as the +/- sign is not integral to the working of this macro, but the problem is that with three different levels of headers the worksheets starts to look a little cluttered and these signs really help.
Finally, my question: Is there any way to trigger a procedure to reset these signs upon/after autofilter? I realize that this is not a built-in Excel event, but a workaround will do just fine. Any way to detect that an autofiltering has taken place without having to check the .Filtermode every second?
Any help would be greatly appreciated. If you think that an example of the code/workbook might help, let me know, and I'll try to strip a small example out of this monster of a spreadsheet.