Slight change to Macro to show subset

EE Pros., I have a great WS that Martin Liss has helped me build out.  I have one "tweak" that I need to the Macro.

Here is what I need;  When you Display the levels you will see in the second Text Box (Blue, Cell C8) there are 3 Sub Text Boxes.  However, when you reset the model, and select the second Text Box (by double clicking in Cell C8), you only get the first Sub Text Box.  The way it should work is when you click on the second Text Box you should see ALL 3 of the Sub Text Boxes below Cell c8.

My belief is that it is a slight change with the helper cells in row A.

Much thanks in advance,

B.
Check-Box-Fix-Before-After.ppt
Impelmentation-Question-Worksheet-needs-
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Change the ShowHideSubset sub to this:

Public Sub ShowHideSubset()
'If a cell in column "C" becomes a check, display all the level 2 rows that belong to the set.
'If the same cell becomes not a check, retract all the level 2 and level 3 rows that belong to the set.
'
'If a cell in column "D" becomes a check, display all the level 3 rows that belong to the set
'If the same cell becomes not a check, retract all the level 3 rows that belong to the set.
'
'If a cell in column "E" becomes a check, don't display or retract any rows in the set (in other words don't do anything)
'If the same cell becomes not a check, don't display or retract any rows in the set (in other words don't do anything)
Dim lngRow As Long
Dim lngStartRow As Long

Application.EnableEvents = False
'ActiveSheet.Unprotect Password:="password"
With ActiveSheet
    If .Cells(ActiveCell.Row, 1) = .Cells(ActiveCell.Row + 1, 1) Then
        ' There are no sublevels so exit
        Application.EnableEvents = True
        Exit Sub
    End If
    If ActiveCell.Column = 3 Then
        ' If the active cell is a checkmark we need to display the level 2 rows
         If ActiveCell = CHECKMARK Then
            lngRow = ActiveCell.Row + 1
            lngStartRow = lngRow
            Do 
                For lngRow = lngStartRow To .UsedRange.Rows.Count + 1
                    If .Cells(lngRow, "A") = "2" Then
                        .Rows(lngRow).Hidden = False
                    ElseIf .Cells(lngRow, 1) = "1" Or .Cells(lngRow, 1) = "" Then
                        Exit Do 
                    End If
                Next
           Loop
        Else
            ' If the activecell is not a checkmark we need to retract both the level 2 and level 3
            For lngRow = ActiveCell.Row + 1 To .UsedRange.Rows.Count + 1
                If .Cells(lngRow, "A") = "1" Then
                    Exit For
                End If
            Next
            .Rows(ActiveCell.Row + 1 & ":" & lngRow - 1).Hidden = True
       End If
    End If
    
    If ActiveCell.Column = 4 Then
        ' Find the last level 3 row for this subset
        For lngRow = ActiveCell.Row + 1 To .UsedRange.Rows.Count + 1
            If Cells(lngRow, "A") <> "3" Then
                Exit For
            End If
        Next
        ' If the active cell is a checkmark we need to display the level 3 rows,
        ' otherwise hide them
        If ActiveCell = CHECKMARK Then
            .Rows(ActiveCell.Row + 1 & ":" & lngRow - 1).Hidden = False
        Else
            .Rows(ActiveCell.Row + 1 & ":" & lngRow - 1).Hidden = True
       End If
    End If
End With
'ActiveSheet.Protect Password:="password"
Application.EnableEvents = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Martin,

Thanks!  

I get an error in this line;

"Unable to set the Hidden Property in the range class."

  .Rows(lngRow).Hidden = False

B.
Martin LissOlder than dirtCommented:
Could you repost your workbook as an excel file rather than as a zip file?

In either case please use a shorter name for the excel file.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Martin LissOlder than dirtCommented:
Actually try this first.

1) Comment out all the lines in the module and sheet where you password protect and unprotect.

2) Then add this codein ThisWorkbook
Private Sub Workbook_Open()
    Sheets("Implemenation Questions").Protect Password:="password", userinterfaceonly:=True
 End Sub

Open in new window


3) Close and reopen the workbook

Note that your sheet name probably should be "Implementation Questions" rather than "Implemenation Questions" which it is now.
Bright01Author Commented:
Martin, as usual.  You were correct!  I worked with your fix on the original file I sent you and it worked!  On the production copy, I simply had to change the password defaults and too then worked fine.

Thanks for the very quick response on this.

You're a great EE Pro.


B.
Martin LissOlder than dirtCommented:
Did you use the method that includes UserInterfaceonly = True? If so you never have to unprotect/protect anywhere else in your code.
Bright01Author Commented:
Martin,

Works great!  I compared both of the answers you subscribed above and could find no difference.  I'm changing the name of the WS to the correct spelling...thanks for the catch.  And as always, you go above and beyond.... thanks for the ability not to have to put in Protect Sheet each time!  The code that you gave me for the WB works well too.

Thanks again.......  

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.