Macro to hide/show different grouped columns

[phil152003]

In my spreadsheet, I have 3 sets of columns that are grouped together separately. I want to have a macro that shows/hides each group.

The problem is that because they are all separately grouped, the groups are all in row 1 in the group bar that appears at the top of the sheet (the part where if you click 1 the groups hide, and if you click 2, the groups unhide).

I found this code, which in effect clicks the "1" or "2", and so all the groups become hidden or unhidden:

Sub Hide_Everything()
'
' Hide_Everything Macro
'
'
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
End Sub
Sub Show_Everything()
'
' Show_Everything Macro
'
'
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
End Sub

But as I initially said, I want a button that will result in, for example, one group unhidden, while the other two remain hidden. Any suggestions? Thanks.

 

[phil152003]

I've had another idea, if anyone could help me. Instead of grouping the columns, and then hiding each group, I could just instead have a macro that hides the specific columns with no need for any grouping.

I've found this code:

Sub Hide_CF()
Columns("D:K").Select
        Selection.EntireColumn.Hidden = True
End Sub
Sub Show_CF()
Columns("D:K").Select
        Selection.EntireColumn.Hidden = False
End Sub

But that leaves the range of cells selected afterwards. I also assume there is a simpler way in vba of doing exactly this, but without selecting the cells. If you can help that would be great!

 

[GlennUK]

Instead of clicking level 1 or level 2, why don't you click the "+" or "-" that shows in the outline area? And why do you want a button to do that click for you?

 

[phil152003]

Because this set of data is going to be sent to a lot of people, some of who might not be so familiar with Excel's grouping function. I figured a simple "click here to show <set of columns 1>", for example, would be the best option to ensure that everyone who looks at this spreadsheet can work it efficiently.

Anyway, I've atually figured it out, and it works really well. Instead of using a button, I've used checkboxes instead. For anyone interested here's what I did:

Private Sub CFCheckBox_Click()
If CFCheckBox.Value = True Then
        Columns("D:K").Hidden = False
End If
If CFCheckBox.Value = False Then
        Columns("D:K").Hidden = True
End If
End Sub

I've just done one of those for each checkbox, and it works brilliantly!