hiding unhiding rows macro hierarchy

David, you will love or hate me after this, but I see several problems with your code and design -- partly because there is plenty of unnecessary code, and I always go for the cleanest and simplest approach, which is more efficient, and easier to maintain and understand. More importantly, you have a lot of hard-coded texts, row numbers, etc., and since I assume that these are just sample goals and tasks (as are the dwarf names), you would have to recode things, not only to make it production-ready, but every time goals, tasks, or dwarfs are added or changed. Writing generic code up front takes a little more design effort, but pays off greatly because it will work without maintenance thereafter.

I'm working on some updates for you, but I'm done for the day, and will get back to you. I didn't want you to think you were being ignored.
-Nate

Sorry, it's been a busy few days and I haven't had much time with this. Question: If I understand correctly, you want to be able to pick the desired tasks for each goal on the Goal sheet, then have it keep those tasks visible and others hidden as you pick different employees on the Tasks sheet. Is the opposite also true (you pick an employee, and then select one or more tasks and the changes apply only to the selected employee)?

Partly. What I'm wondering is, let's say Happy selects Goal 1, Task A, then on the Tasks sheet, selects Happy. It now shows only Goal 1 Task A for Happy. If he goes back to Goals and selects Goal 1, Task B, then goes back to Tasks, should Happy still be selected, but now Task A is hidden and Task B for Happy is showing? I'm probably way off on the scenario.

I didn't need to insert a column B, since it was already blank. See if this will be a good start for you.

Instead of buttons on each column, I added a combo box (cboEmpList) to select the employee you want. It is populated from a list on a new Employees sheet, which you will need to maintain to match the names in TASKS row 1.

Select the desired tasks on the GOALS sheet, then switch to TASKS and pick the desired employee. The showing/hiding of rows and columns is triggered by the TASKS WorksheetActivate event, as well as by the cboEmpList_Change event. The code should work automatically as is when you add new goals/tasks in GOALS columns B:J, and as you add/change/delete employees on TASKS and Employees. However, if you add or change goals/tasks, then switch to the TASKS sheet, it may display error messages trying to find them (until you get them added there also).

Have fun.

Dwarfs.xlsm

Ok, one more thing. On your original example wkbk, you have Goal/Task selectable on the GOALS sheet (eg, Goal 1, Task A), and on TASKS you have the same goals/tasks, but with sub-tasks (Goal 1, TaskA1; Goal1, TaskA2, etc.) For a generic solution, you would need a way of linking these sub tasks with their goal/task. I suggest inserting a new column B on TASKS for the sub-task name. So, cells A5 & A6 would have "Goal 1, Task A", B5 would have "A1", B6 would have "A2", etc. Then when you select Goal 1, Task 1, the code would have something to look for in Column A. Would that work for you?

Unfortunately I'm unable to see your attachments. They just show up as a black X with no functionality. I've seen this before but don't know what to do about it. It's hard to provide code blind. Maybe you could attach a workbook with just the Goals sheet or something.

Your checkbox1 code could be improved as this:

Private Sub CheckBox1_Click()
    Columns("J").Hidden = Not CheckBox1.Value
    Columns("T").Hidden = Not CheckBox1.Value
End Sub

Ok, I'll take a look at this next week.

David,
You've clearly done a lot of work since the original, and understandably can't post private data. Since you're attachment doesn't include the Tasks sheet, which is where the code was that I had provided, I'm starting with my original code, and you'll need to make any adjustments. The following has the original loop (now pointing to Scorecard instead of Goals, and with tasks in columns G instead of B), but instead of the column numbers being hard-coded, they are in a variable. When it hits the end of the first set, it resets the row & col numbers and continues on the 2nd set. I couldn't test, but it should be close.

BTW, I didn't know Dwarves yodeled. I thought it was just the Hi Ho thing.

Option Explicit
Dim GoalTxt As String, TaskTxt As String, EmpTxt As String, EmpCol As Long
Dim BegRw As Long, EndRw As Long, TaskEndRw As Long, GoalRw As Long, GoalCol As Long, Col As Long
   
Private Sub Worksheet_Activate()
    With Sheets("SCORECARD")
        If .Range("SelectionChanged").Value = True Then
            .Range("SelectionChanged").Value = False
            Call ShowGoalsAndTasks
            Sheets("Employees").Range("EmpSelection").Value = "All Employees"
        End If
    End With
End Sub

Sub ShowGoalsAndTasks()
    Application.ScreenUpdating = False

    BegRw = 3
    GoalRw = 7
    GoalCol = 10 ' Col J
    Rows(BegRw & ":" & Rows.Count).Hidden = False
    TaskEndRw = Range("A" & Rows.Count).End(xlUp).Row

    ' Start with all rows hidden
    Rows(BegRw & ":" & TaskEndRw).Hidden = True

    With Sheets("SCORECARD")
        Do Until .Cells(GoalRw, GoalCol).Value = "" ' Loop through selected tasks
            GoalTxt = .Cells(GoalRw, GoalCol - 3).Value
            TaskTxt = .Cells(GoalRw, GoalCol).Value
            Do Until Cells(BegRw, 1).Value = GoalTxt Or BegRw > TaskEndRw
                BegRw = BegRw + 1 ' Find selected goal
            Loop
            If BegRw > TaskEndRw Then
                MsgBox GoalTxt & " goal not found."
            Else ' Found it
                If TaskTxt = "Not Pursuing" Then
                    Rows(BegRw - 1).Hidden = False
                Else
                    Rows(BegRw).Hidden = False ' Goal Name
                    BegRw = BegRw + 1
                    EndRw = BegRw
                    If TaskTxt = "Pursuing - Show All Tasks" Then ' Find the end of this goal section
                        Do Until Left(Cells(EndRw, 1).Value, 12) = "Not Pursuing" Or EndRw > TaskEndRw Or _
                          Cells(EndRw, 1).Value = .Cells(GoalRw + 1, 2).Value
                            EndRw = EndRw + 1
                        Loop
                    Else
                        Do Until Cells(BegRw, 1).Value = TaskTxt Or BegRw > TaskEndRw
                            BegRw = BegRw + 1 ' Find selected task
                        Loop
                        If BegRw > TaskEndRw Then
                            MsgBox TaskTxt & " task not found."
                        Else ' Found it
                            EndRw = BegRw + 1
                            Do Until Cells(EndRw, 1).Value <> TaskTxt
                                EndRw = EndRw + 1 ' Find end of selected task
                            Loop
                        End If
                    End If
                    If Not BegRw > TaskEndRw Then
                        Rows(BegRw & ":" & EndRw - 1).Hidden = False
                    End If
                End If
            End If
            GoalRw = GoalRw + 1
            If .Cells(GoalRw, GoalCol).Value = "" And GoalCol = 10 Then ' Start next set of columns
                GoalRw = 7
                GoalCol = 20 ' Col T
            End If
        Loop
    End With

    ' Start with all employee columns visible
    Columns("C:AZ").Hidden = False
End Sub

Yes, empty rows were not being handled, and it didn't recover well from missing goals or tasks either (for example, your Scorecard had "Required", but on Tasks, it was "Required Task". Replace the ShowGoalsAndTasks subroutine with this and try again. I highlighted the changes.

Sub ShowGoalsAndTasks()
    Application.ScreenUpdating = False

    BegRw = 3
    GoalRw = 7
    GoalCol = 10 ' Col J
    Rows(BegRw & ":" & Rows.Count).Hidden = False
    TaskEndRw = Range("A" & Rows.Count).End(xlUp).Row

    ' Start with all rows hidden
    Rows(BegRw & ":" & TaskEndRw).Hidden = True

    With Sheets("SCORECARD")
        Do Until .Cells(GoalRw, GoalCol).Value = "" ' Loop through selected tasks
            GoalTxt = .Cells(GoalRw, GoalCol - 3).Value
            TaskTxt = .Cells(GoalRw, GoalCol).Value
            Do Until Cells(BegRw, 1).Value = GoalTxt Or BegRw > TaskEndRw
                BegRw = BegRw + 1 ' Find selected goal
            Loop
            If BegRw > TaskEndRw Then
                MsgBox GoalTxt & " goal not found."
                BegRw = 3
            Else ' Found it
                If TaskTxt = "Not Pursuing" Then
                    Rows(BegRw - 1).Hidden = False
                Else
                    Rows(BegRw).Hidden = False ' Goal Name
                    BegRw = BegRw + 1
                    EndRw = BegRw
                    If TaskTxt = "Pursuing - Show All Tasks" Then ' Find the end of this goal section
                        Do Until Left(Cells(EndRw, 1).Value, 12) = "Not Pursuing" Or EndRw > TaskEndRw Or _
                          Cells(EndRw, 1).Value = .Cells(GoalRw + 1, 2).Value
                            EndRw = EndRw + 1
                        Loop
                    Else
                        Do Until Cells(BegRw, 1).Value = TaskTxt Or BegRw > TaskEndRw
                            BegRw = BegRw + 1 ' Find selected task
                        Loop
                        If BegRw > TaskEndRw Then
                            MsgBox TaskTxt & " task not found."
                            BegRw = 3
                        Else ' Found it
                            EndRw = BegRw + 1
                            Do Until Cells(EndRw, 1).Value <> TaskTxt
                                EndRw = EndRw + 1 ' Find end of selected task
                            Loop
                        End If
                    End If
                    If Not BegRw > TaskEndRw Then
                        Rows(BegRw & ":" & EndRw - 1).Hidden = False
                    End If
                End If
            End If
            If .Cells(GoalRw + 1, GoalCol).Value = "" Then
                GoalRw = .Cells(GoalRw + 1, GoalCol).End(xlDown).Row
            Else
                GoalRw = GoalRw + 1
            End If
            If .Cells(GoalRw, GoalCol).Value = "" And GoalCol = 10 Then ' Start next set of columns
                GoalRw = 7
                GoalCol = 20 ' Col T
                If .Cells(GoalRw, GoalCol).Value = "" Then
                    GoalRw = .Cells(GoalRw + 1, GoalCol).End(xlDown).Row
                End If
            End If
        Loop
    End With

    ' Start with all employee columns visible
    Columns("C:AZ").Hidden = False
End Sub

In your original sample, there was data in column B of Tasks. The code used that to determine if it was a task row or a goal row. For whatever reason, column B is now entirely blank, so it didn't work right anymore.

In the Tasks sheet code module, in the cboEmpListChange event, find this:

            If Cells(EndRw, 1).EntireRow.Hidden = False Then
                If Cells(EndRw, 2).Value <> "" Then
                    If Cells(EndRw, EmpCol).Value = "" Then
                        Rows(EndRw).Hidden = True
                    End If
                End If
            End If

and replace it with this:

            If Cells(EndRw, 1).EntireRow.Hidden = False Then
                If Cells(EndRw, EmpCol).Value = "" Then
                    Rows(EndRw).Hidden = True
                End If
            End If

Hopefully everything else will still work like it should.

It appears you have deleted a bunch of code in the Tasks sheet code module. Go back to post #17 (or a backup copy of your own) to retrieve it, but after doing so, be sure to adjust for the changes to the ShowGoalsAndTasks subroutine in post #18.

Regarding column B: In your original examples, on the Tasks sheet you had the goals and tasks in column A, and sub-tasks in column B. You still have goals and tasks in column A, but nothing in column B, which results in what appears to be duplicate tasks (such as the three "Required Task" entries under Clear Throat). If you were to put the sub-tasks back into column B, and then not apply the code changes from post #20, it would take care of the goal title not appearing for individual workers, but then all goal titles will appear, whether there any task numbers entered for that employee or not. If that's not ok, additional coding would be needed.