98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Write Macro to group & ungroup
Write Macro to group & ungroup
Resolved · High Priority · Version 2013
Thu 10 May 2018, 20:19Delegate Tsudoi said...
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,
Thu 10 May 2018, 21:23Trainer Jens said...
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
Thu 10 May 2018, 21:39Delegate Tsudoi said...
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
Thu 10 May 2018, 21:54Trainer Jens said...
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
Thu 10 May 2018, 22:05Delegate Tsudoi said...
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
Thu 10 May 2018, 22:55Trainer Jens said...
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
Thu 17 May 2018: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Move data worksheet to worksheetTo move data from one worksheet to another, highlight the data. |
