hiding unhiding rows macro hierarchy

**natefarm** · 10-06-2014, 03:57 PM

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

**dgibney** · 11-07-2014, 05:31 PM

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:

Sub Section1Code(Target)
Dim a As Double
a = Application.WorksheetFunction.Sum(Range("B7:D7"))
If a < Range("I7") Then
    Range("B7:D7").Interior.Color = RGB(255, 165, 0)
    Else
If a = Range("I7") Then
    Range("B7:D7").Interior.Color = RGB(216, 228, 188)
        End If
    End If
End Sub

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:

Private Sub CheckBox1_Click()
If Sheets("SCORECARD").CheckBox1.Value = False Then
    Call Hide_JT
    End If
If Sheets("SCORECARD").CheckBox1.Value = True Then
    Call Unhide_JT
    End If
End Sub
Sub Hide_JT()
ActiveWorkbook.Sheets("SCORECARD").Columns("J").EntireColumn.Hidden = True
ActiveWorkbook.Sheets("SCORECARD").Columns("T").EntireColumn.Hidden = True
End Sub
Sub Unhide_JT()
ActiveWorkbook.Sheets("SCORECARD").Columns("J").EntireColumn.Hidden = False
ActiveWorkbook.Sheets("SCORECARD").Columns("T").EntireColumn.Hidden = False
End Sub

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ValidationType As Variant
    
    If Target.Cells.Count = 1 Then
        If Not Intersect(Range("J:J,T:T"), Target) Is Nothing Then
            On Error Resume Next
            ValidationType = Target.Validation.Type
            On Error GoTo 0
            If Not IsEmpty(ValidationType) Then
                Application.SendKeys "%{DOWN}"
            End If
        End If
    End If
            
End Sub

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