Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course
Write Macro to group & ungroup
Hi there,
I have a file where many columns are grouped.
And so I would like to insert a button and assign macro to do grouping/ungrouping.
For example, click the button to group these columns and click again to ungroup.
Shouldn't be a complicated micro I imagine but how best to do it?
Please advise?
Thanks,
RE: Write Macro to group & ungroup
Hi Tsudoi,
Thank you for the forum question.
You will need some knowledge of event VBA to do exactly what you want or knowledge of static declared variables.
It is more simple if you use a button to group and another button to ungroup.
Insert a Command button from the Developer tab's Control group.
In the button click event the code is:
Columns("B:F").Select
Selection.Columns.Group
If it is column B to F you want to group.
For the ungroup button the code is:
Columns("B:F").Select
Selection.Columns.Ungroup
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Write Macro to group & ungroup
Hi Jens,
Thanks for your reply and sorry I did not explain my inquiry enough.
The file I was talking about has many column groupings across many sheets. So I would like the button to cover all the groupings in the whole workbook and do so by clicking on the same button (both grouping and ungrounding).
So for example, have the button with text that reads "Click to Ungroup" and once clicked, the text changes and reads "Click to Gruoup" using the same button.
Thanks,
Tsudoi
RE: Write Macro to group & ungroup
Hi Tsudoi,
This is not a simple task.
Can I have a look at the code you have so far.
Please copy your code and paste it in the forum.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Write Macro to group & ungroup
Hi Jens,
How can I improve this to do what I want to achieve for the whole workbook or this is completely incorrect?
Sub Test()
On Error Resume Next
If Sheet11.cmdTest.Caption = "Click to Ungroup" Then
ActiveSheet.Outline.ShowLevels ColumnLevels:=2
Sheet11.cmdTest.Caption = "Click to group"
Else
ActiveSheet.Outline.ShowLevels ColumnLevels:=1
Sheet11.cmdTest.Caption = "Click to Ungroup"
End If
End Sub
Thanks,
Tsudoi
RE: Write Macro to group & ungroup
Hi Tsudoi,
To me it looks like you want to change the outline level not group and ungroup.
You cannot reference the caption you must make a button click event.
As I mentioned this is a very complicated task and you will need to learn how to loop through worksheets and you will also have to loop through the columns to test the outline level.
The link below is the syntax for the outlinelevel property you will need to test.
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-outlinelevel-property-excel
and the code below is to set the outline level:
ActiveSheet.Outline.ShowLevels ColumnLevels:=1
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
