Collapsable Cells

[mmdmalta]

having some difficulty figuring out how to collapse multiple rows. Here is my example of what i'm trying to do. I already understand the Checkbox VBA codes etc. Here is the formula VBA i'm putting in. Private Sub CheckBox1_Click()
[A30:H65].EntireRow.Hidden = CheckBox1
End Sub

Private Sub CheckBox2_Click()
[A14:H30].EntireRow.Hidden = CheckBox2
End Sub

Private Sub CheckBox3_Click()
[A14:H41].EntireRow.Hidden = CheckBox3
End Sub

Private Sub CheckBox4_Click()
[A14:H50].EntireRow.Hidden = CheckBox4
End Sub

Private Sub CheckBox5_Click()
[A14:H56].EntireRow.Hidden = CheckBox5
End Sub

I have 4 sections in the worksheet. If I click on one section, I want all the others to collapse. However, If i click on two sections, then I want both sections to stay open when i click on both sections, and the remaining collapsed. Same if i want 3 sections, and do on.... I was able to get the first part to work, but i can't figure out how to have multiple sections open, at the same time while having other collapsed. What am i doing wrong?

 

[jproffer]

Your sections overlap. If I remember correctly, a checkbox being checked has a value of True...so Hidden = True.

Rows 13 thru 30 will always be hidden if any of the CB's are checked. Rows 14 thru 41 is CB3, 4, or 5......and so on.

 

[mmdmalta]

Your sections overlap. If I remember correctly, a checkbox being checked has a value of True...so Hidden = True.

Rows 13 thru 30 will always be hidden if any of the CB's are checked. Rows 14 thru 41 is CB3, 4, or 5......and so on.

Thank you... I'll give it a try...

 

[mmdmalta]

Hi I guess i'm not clear on your explaination..... Can you tell me from below, how my formula should read?

Private Sub CheckBox2_Click()
[A14:H28].EntireRow.Hidden = CheckBox2
End Sub

Private Sub CheckBox3_Click()
[A14:H41].EntireRow.Hidden = CheckBox3
End Sub

Private Sub CheckBox4_Click()
[A14:H50].EntireRow.Hidden = CheckBox4
End Sub

Private Sub CheckBox5_Click()
[A14:H56].EntireRow.Hidden = CheckBox5
End Sub

 

[MickG]

Try this code:-

Private [COLOR=navy]Sub[/COLOR] CheckBox2_Click()
    RoHide
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

 Private [COLOR=navy]Sub[/COLOR] CheckBox3_Click()
    RoHide
 [COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 
Private [COLOR=navy]Sub[/COLOR] CheckBox4_Click()
    RoHide
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

 Private [COLOR=navy]Sub[/COLOR] CheckBox5_Click()
    RoHide
 [COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]

Sub[/COLOR] RoHide()
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range, Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]For[/COLOR] n = 2 To 5
    [COLOR=navy]If[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Object.Value = True [COLOR=navy]Then[/COLOR]
        [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] ActiveSheet.OLEObjects("CheckBox" & n).Name
            [COLOR=navy]Case[/COLOR] "CheckBox2": [COLOR=navy]Set[/COLOR] R = Range("A14:A28")
            [COLOR=navy]Case[/COLOR] "CheckBox3": [COLOR=navy]Set[/COLOR] R = Range("A14:A41")
            [COLOR=navy]Case[/COLOR] "CheckBox4": [COLOR=navy]Set[/COLOR] R = Range("A14:A50")
            [COLOR=navy]Case[/COLOR] "CheckBox5": [COLOR=navy]Set[/COLOR] R = Range("A14:A56")
        [COLOR=navy]End[/COLOR] Select
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]If[/COLOR] Rng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Set[/COLOR] Rng = R Else [COLOR=navy]Set[/COLOR] Rng = Union(Rng, R)
[COLOR=navy]Next[/COLOR] n
Range("A:A").EntireRow.Hidden = False
[COLOR=navy]If[/COLOR] Not Rng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] Rng.EntireRow.Hidden = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[mmdmalta]

Thank you Mick.... unfortuntly i received an compile error - Expected End Sub and it highlighed in yellow the Sub Rohide() (last section) with another highlight on the last line first command If RNG. I apprectiate you trying to help, i don't want to take up anymore of your time. If it is a quick fix, great, otherwise don't worry about it

Thanks again

Private Sub CheckBox1_Click()
RoHide
End Sub

Private Sub CheckBox2_Click()
RoHide
End Sub

Private Sub CheckBox3_Click()
RoHide
End Sub

Private Sub CheckBox4_Click()
RoHide
End Sub

Private Sub CheckBox5_Click()
RoHide
End Sub


Sub RoHide()
Dim n As Long, R As Range, Rng As Range
For n = 2 To 5
If ActiveSheet.OLEObjects("CheckBox" & n).Object.Value = True Then
Select Case ActiveSheet.OLEObjects("CheckBox" & n).Name
Case "CheckBox2": Set R = Range("A14:A28")
Case "CheckBox3": Set R = Range("A14:A41")
Case "CheckBox4": Set R = Range("A14:A50")
Case "CheckBox5": Set R = Range("A14:A56")
End Select
End If
If Rng Is Nothing Then Set Rng = R Else Set Rng = Union(Rng, R)
Next n

 

[jproffer]

If you copied that from your module then you missed something when you copied it from here. Very bottom line should be End Sub.

 

[MickG]

You've actually missed the last 3 lines from My code !!!!