hiding unhiding rows macro hierarchy

Depending on which goals are selected from pull-down menus in the “Goals” sheet this tool creates customized task lists by hiding or unhiding rows in the “Tasks” sheet. By selecting a button team members can sort their own tasks if a value (labor hours) has been entered into the appropriate task row and beneath their column. By pushing this button again all tasks and all team members columns are unhidden.
PROBLEM: how to keep rows hidden or unhidden based on the selection in the pull-down menu in the “Goals” sheet. Please see attached document.The command to unhide all tasks unfortunately unhides all rows, including those that are hidden. Is there a line of code I can enter into the “DocTasksOnly” macro that will direct it to ignore hidden rows? Or, once the task list is filtered for a team member, is there a way to auto-refresh the hiding/unhiding of task rows? By selecting different goals and toggling the button back and forth you will better be able to understand the dilemma I've got myself into...
Note that hiding/unhiding macros are provided only for “Doc” in this file example. In the final these macros will be replicated for each team member.
Thanks for your help! David

"Goals" code:

HTML Code: 

"Tasks" sheet code:

HTML Code: 

"Hide_Unhide_Task_RowsColumns" code:

HTML Code: 

Nate, I absolutely do NOT hate you for your frankness. Until I started on the creation of this tool several months ago I had never written a macro. If I can get this tool to work it will help hundreds of people on design teams create better buildings. The actual tool has about 750 tasks and about a dozen team members. And perhaps 24 goals, each with pull-down menus. So, it's complex. FYI, it's not a tool we will sell, just share with teams to use as a project management aid.

I look forward to seeing what you come up with and am very appreciative. Thank you Nate. David

Psalm 8 (my favorite in the OT)
John 11:35 (my favorite in the NT as it helps remind me how much He understands us.)

Here's ultimately my problem: the "Task" rows should stay hidden or revealed, depending on what "instructions" they receive from the "Goals" sheet worksheet change code. I've had problems with rows that were hidden being unhidden when sorting. My strategy was to try to re-trigger the worksheet change event (that initiates this hiding/unhiding)at the end of a series of macros, so that no matter what sorting of tasks (by person) only the correct tasks are ever revealed. It's almost like an issue of hierarchy: first the tasks must be established (hidden or not hidden). Then Only from those tasks that are not hidden can any hours be assigned. The hidden rows must always remain hidden (unless a different option is selected from the pull-down menu. In which case the hidden/unhidden rows auto-adjust). And, for what it's worth, the team member sorting should be only one person at a time OR the entire team. No two people's tasks (from the "unhidden" pool of tasks!) showing.

Does that help describe what I'm looking to achieve? If not please let me know. Thanks again Nate for your willingness to help. I truly appreciate it! David

I think you've got it, but let me elaborate, just to be sure... (And I apologize it's so confusing.)

First, individual team members will not select which goals they want to pursue, the team will select them collectively. So, all team members will pursue only the pool of tasks that are not hidden in the "Tasks" sheet.

Second, For each row usually only one team members will be assigning hours, but not all. What's important is for the rows of tasks that are supposed to be revealed are always revealed except when a team member is filtering the list to only see those (revealed)tasks for which they have hours assigned in their column.
Third, when they've done this and want to un-filter the rows (by clicking the black "Show All Team Tasks" button) only those tasks that should be revealed are revealed. I've had problems with the Worksheet change macro not re-directing the hiding or unhiding or rows correctly when clicking this black button.

Another way to look at it is a matter of hierarchy. After any filtering/unfiltering there should be a routine to verify that only the correct rows are revealed per what was selected in the "Goals" sheet.

Thanks again Nate! David

Hi Nate. Sorry for my delayed response. I've been tasked with something urgent and due for this Friday. I played around with your solution. Very interesting! Yes, I think it will work, but I may end up hiding a few of the referenced lists. The final product I am creating has about 30 tasks and each will have 1-5 options, with about 650 total rows of individual tasks to hide/unhide. Let me take a week and see if I can (1) figure out what your code is doing (and thanks for the notes, BTW) and (2) see if I can get to work on a bigger scale. I will get back to you in about a week. THANK YOU so much for this!
David

Hi Nate, I apologize for being absent for a month. I had a heavy task placed on me and couldn't spend time on this. I have a few questions for you. The tool that I'm building has enough goals that in the preferred format they are arranged in two "columns" to fit an 8.5 X 11 cleanly. Please take a look at the "Dwarf Scorecard" attachment to get an idea of what the finished product is intended to look like. I need your help figuring out how to allow two "columns" of goals, with tasks. There are also a few features that I did not describe to you, any of which may interfere with your code. (I really like your solution, BTW. Thanks.) Could you please take a look at these below and let me know if you see any issues?

The first feature is that each goal is assigned credit points in Yes|Maybe|No columns and these points are tallied to provide a final score. There are a maximum number of points possible for each goal and these are listed in columns "I" and "S". Now take a look at the second attachment "Dwarf point control". When the total number of points in any row does not equal the Possible Point value, all three cells (Yes|Maybe|No) are orange. When the point tally does equal the Possible Points these cells are light green. This is the macro I used to achieve this:

Please Login or Register  to view this content.

If the total points exceed the value an error message pops up. This is via Data Validation.

Finally, to limit the scorecard to just show the goals and credit point distribution I've inserted a button to open or close columns "J" and "T". The code for that is straightforward:

Please Login or Register  to view this content.

I also made the pull-downs auto-reveal so clicking on the side button isn't required. Code:

Please Login or Register  to view this content.

Would you mind taking a stab at expanding the original Dwarf document to include two "Columns" and the credit point columns? For it's worth this "Scorecard" will be the final format in terms of total columns and rows. The total number of affiliated Tasks is over six hundred.

Thanks Nate. I really appreciate any direction you can provide. David

Oops, here are the attachments. Sorry.

Sorry about that. Here is the workbook without the Tasks sheet. (They're confidential...) The color switching feature in the "Yes|Maybe|No columns doesn't work now but you can always add the code I uploaded if you like.

I will clean up the checkbox 1 code. It's always so interesting to see how people do things different with these macros.

Thanks again Nate. David

Thanks Nate, it's not quite working yet. The attached file is a shorter version of the spreadsheet screen shot I sent, with two columns of goals and options. I replicated your other inner workings per the first example you sent. The employee columns are hiding/unhiding correctly. I also expanded the team roster to include a few placeholders.

The very first goal functions properly but everything past it remains hidden in the "Tasks" sheet. Could it be the empty row/cells that follow it are causing the problem? Also, please note that there are required Goals in red. These will have rows of tasks, always visible, and there is no pull-down menu of options for these required tasks. I hope this doesn't cause any hiccups.

I added some NOTES in the "Scorecard" code to explain what some of the other features are supposed to do.

If you can take a look through this hopefully the fix will be simple. I'm sure I'm missing something, probably elementary.

As far as the Hi Ho yodel thing, you're correct. I made it up...

I'm very grateful for your help. Thanks Nate. David

We're almost there! Just one small issue. Please see attached version 3. In order for the Tasks rows to hide/unhide I have to change the EmpList checkbox listing on the Tasks sheet. Other than that it's working fine! Am I correct that we need to add some event to toggle the SelectionChange box to "False"? Right now it always reads "True"....If it makes this run smoother I can always add a one-line Data Validation list ("REQUIRED") for the Required Tasks. I tried it and it works but it didn't make any difference with the current updating/refreshing issue.

This is very encouraging Nate. I've been plugging away on this for several months and you've been so helpful. Thank you. David

Hmm, I don't think that fixes the problem, but, before making the deduction...do I need to add data in Column "B"? I played with it in earlier versions and it didn't seem to make any difference. If I do need to include data in column B is there a special sequence or exact matching text I need to be aware of? With this version (attached spreadsheet) when I click on the "Tasks" tab it still doesn't hide/unhide until I click on a different name. And, here's something interesting.....when I click on the "All Employees" entry it includes the Task title above each task, but whenever a team member is selected these titles are not revealed.

Almost there...... Thanks Nate, I appreciate your patience with this weird set of issues. David