Expand and collapse groups using vba

[ken1921]

Hi

Is it possible to expand and collapse individual groups using vba i.e. as opposed to expanding or collapsing all groups.

In the following example I would like to have 3 buttons somewhere on the sheet, one for each group, to toggle expand collapse each group one at a time

[/IMG]

 

[RickXL]

Hi,

I think the way to go is to either Hide or Unhide the columns. The Outline object does not appear to be much use for this operation. So you could have four buttons: One to hide the first group, one to hide the second group, one to hide the second group and one to show everything.

Private Sub CommandButton1_Click()
    Columns("B:D").Hidden = True
    Columns("G:I").Hidden = False
    Columns("L:N").Hidden = False
End Sub
Private Sub CommandButton2_Click()
    Columns("B:D").Hidden = False
    Columns("G:I").Hidden = True
    Columns("L:N").Hidden = False
End Sub
Private Sub CommandButton3_Click()
    Columns("B:D").Hidden = False
    Columns("G:I").Hidden = False
    Columns("L:N").Hidden = True
End Sub
Private Sub CommandButton4_Click()
    Columns("B:D").Hidden = False
    Columns("G:I").Hidden = False
    Columns("L:N").Hidden = False
End Sub

An alternative would be to make the buttons toggle. You could do this by asking whether the columns were hidden and then doing the opposite.

Private Sub CommandButton1_Click()
    If Columns("B:D").Hidden Then Columns("B:D").Hidden = False Else Columns("B:D").Hidden = True
End Sub
Private Sub CommandButton2_Click()
    If Columns("G:I").Hidden Then Columns("G:I").Hidden = False Else Columns("G:I").Hidden = True
End Sub
Private Sub CommandButton3_Click()
    If Columns("L:N").Hidden Then Columns("L:N").Hidden = False Else Columns("L:N").Hidden = True
End Sub

Regards,

 

[Peter_SSs]

An alternative would be to make the buttons toggle.

Private Sub CommandButton1_Click()
    If Columns("B:D").Hidden Then Columns("B:D").Hidden = False Else Columns("B:D").Hidden = True
End Sub

Alternatively ..

Private Sub CommandButton1_Click()
   Columns("B:D").Hidden = Not Columns("B:D").Hidden
End Sub

 

[RickXL]

That's neat Peter, I hate it when lines get too long!


Regards,