Toggle Switch to hide/show column groups.

[MLijoi]

Hello, I was trying to create a button to hide or show columns that I have grouped, but for some reason I cannot do this using the record function. Is there another way to do this? Also, I already have a "Reset" button on the same spreadsheet that clears the filters, is this why I am having the issue? Would it be better to use a toggle switch instead or option buttons? Is there a way to have my "Reset" button also effect the column filters to default back to an unhidden appearance? This will eventually be a protected document that many will use.

Thank You in Advance for all of your help.

[Ron Coderre]

With this sample data, grouped by quarters (Q1, Q2, etc) and by Year:
Data Range

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	Jan	Feb	Mar	Q1	Apr	May	Jun	Q2	Jul	Aug	Sep	Q3	Oct	Nov	Dec	Q4	Year
2	Alpha	1	1	1	3	2	2	2	6	3	3	3	9	4	4	4	12	30
3	Bravo	1	1	1	3	2	2	2	6	3	3	3	9	4	4	4	12	30
4	Charlie	1	1	1	3	2	2	2	6	3	3	3	9	4	4	4	12	30

This VBA code sets the worksheet outline level to 2 (so all quarters are collapsed) then expands Q1 (so Jan, Feb and Mar display)

Sub TestGroups()
Dim wks As Worksheet
Dim rng As Range
Set wks = ActiveSheet
Set rng = [C1]

wks.Outline.ShowLevels ColumnLevels:=2
rng.EntireColumn.ShowDetail = True

End Sub

Is that something you can work with?

[MLijoi]

Mr. Coderre,

Thank You for your quick response. I think my initial thread was a little confusing on what I was trying to do. I went ahead an attached the spreadsheet. I'm trying to use the Hide / Show option buttons to hide/show the columns that I have set as groups. Then I wanted to include the "show" as the default to the reset button. (Not sure if that is possible, but figured I would ask.

I appreciate all of your help.

Thanks,
Mike

[Ron Coderre]

using your posted workbook....

Put this VBA code in your module:

Sub ToggleColView()
Dim wks As Worksheet
Dim shpOptHide As Shape
Dim shpOptShow As Shape

Set wks = Worksheets("Refrigeration Units")
Set shpOptHide = wks.Shapes("Option Button 8")
Set shpOptShow = wks.Shapes("Option Button 9")

If shpOptHide.ControlFormat.Value = xlOn Then
    ' MsgBox "You clicked: Hide"
    wks.Outline.ShowLevels ColumnLevels:=1
ElseIf shpOptShow.ControlFormat.Value = xlOn Then
    ' MsgBox "You clicked: Show"
    wks.Outline.ShowLevels ColumnLevels:=8
End If

End Sub

That code toggles the display/hide of the grouped columns.

Assign both option buttons to that macro.

Is that something you can work with?

[MLijoi]

Worked perfectly, thank you