Selectively Stacked Columns in a Chart
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Selectively Stacked Columns in a Chart
I was recently shown the a technique to selectively stack some but not necessarily all columns of a chart. Unfortunately the process is somewhat long and prone to human error. With the deepest respect for Stephen Bullen (the author of the first example), and SteveA, I climbed up on their shoulders and developed the attached.
I hope that it is deemed to be of some use.
Edit follows:
Following posts indicated the need for some tuning. The original attachment has been removed, The updated version can be found here.
I hope that it is deemed to be of some use.
Edit follows:
Following posts indicated the need for some tuning. The original attachment has been removed, The updated version can be found here.
Last edited by Don Wells on 27 Nov 2010, 15:04, edited 1 time in total.
Regards
Don
Don
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selectively Stacked Columns in a Chart
Thanks, it's an interesting concept - setting up the data correctly is certainly complicated. But it appears to need a bit of fine-tuning.
I specified M1 and M2 to be stacked, and SP and OI to be stacked, while M1+M2 was to be remain unstacked. The result looks like this:
Series OI is missing, and there is an unnamed series without data (it turns out to be column B which is empty).
I specified M1 and M2 to be stacked, and SP and OI to be stacked, while M1+M2 was to be remain unstacked. The result looks like this:
Series OI is missing, and there is an unnamed series without data (it turns out to be column B which is empty).
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Selectively Stacked Columns in a Chart
I just thought the original post and discussion which lead to Don's code should be referenced:
http://lounge.windowssecrets.com/index. ... pic=779979
Steve
http://lounge.windowssecrets.com/index. ... pic=779979
Steve
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
Thank you for the feedback Hans.HansV wrote:Thanks, it's an interesting concept - setting up the data correctly is certainly complicated. But it appears to need a bit of fine-tuning.
I specified M1 and M2 to be stacked, and SP and OI to be stacked, while M1+M2 was to be remain unstacked. The result looks like this:
Series OI is missing, and there is an unnamed series without data (it turns out to be column B which is empty).
- I'm running 2003
- Column B should not be empty. Each cell should contain a single space.
- Setting the same requirements as you and placing the chart with the table; I received the following results.
You do not have the required permissions to view the files attached to this post.
Regards
Don
Don
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selectively Stacked Columns in a Chart
I'm using Excel 2007 SP2, and it turns out that the legend entry of the dummy series is the last one instead of the first one. So if I change the line
ActiveChart.Legend.LegendEntries(1).LegendKey.Select
to
ActiveChart.Legend.LegendEntries(ActiveChart.Legend.LegendEntries.Count).LegendKey.Select
the code works perfectly. Perhaps this will work in all versions:
Check carefully whether this works in Excel 2003; it does in Excel 2007.
ActiveChart.Legend.LegendEntries(1).LegendKey.Select
to
ActiveChart.Legend.LegendEntries(ActiveChart.Legend.LegendEntries.Count).LegendKey.Select
the code works perfectly. Perhaps this will work in all versions:
Code: Select all
Dim intItem As Integer
If Val(Application.Version) < 12 Then
intItem = 1
Else
intItem = ActiveChart.Legend.LegendEntries.Count
End If
ActiveChart.Legend.LegendEntries(intItem).LegendKey.Select
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
Thank you HansHansV wrote:I'm using Excel 2007 SP2, and it turns out that the legend entry of the dummy series is the last one instead of the first one. So if I change the line ...
I have incorporated and tested your recommended change. It works as I expected.
I have also inhibited the screen updating with the start the chart creation to stop the screen flicker when placing the chart on a chart sheet.
I have attached a workbook with the new code here, and will remove the attachment from the previous post.
Edit Details
The code was revised in accordance with this post and the replacement file has been posted here.
Edit 28 Nov
I have discovered a bug in my code. I am removing all posted copies until it is repaired. Humblest apologies. I will be back.
Last edited by Don Wells on 29 Nov 2010, 01:17, edited 2 times in total.
Regards
Don
Don
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selectively Stacked Columns in a Chart
Great - your new version works well in Excel 2007 too!
If you're really ambitious, you could create an Excel add-in with a custom toolbar so that the macro can be invoked from any workbook.
If you're really ambitious, you could create an Excel add-in with a custom toolbar so that the macro can be invoked from any workbook.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
That just got added to my To-Do listHansV wrote:Great - your new version works well in Excel 2007 too!
If you're really ambitious, you could create an Excel add-in with a custom toolbar so that the macro can be invoked from any workbook.
Regards
Don
Don
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
Please see attachedHansV wrote: If you're really ambitious, you could create an Excel add-in with a custom toolbar so that the macro can be invoked from any workbook.
Edit Details
The code was revised in accordance with this post and the replacement file has been posted here.
Edit 28 Nov
I have discovered a bug in my code. I am removing all posted copies until it is repaired. Humblest apologies. I will be back.
Last edited by Don Wells on 29 Nov 2010, 01:16, edited 2 times in total.
Regards
Don
Don
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selectively Stacked Columns in a Chart
I don't see a custom toolbar in Excel 2007.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
HansV wrote:I don't see a custom toolbar in Excel 2007.
- I installed the Add-In
- closed Excel. and relaunched it
- Under Tools > Customize: Deleted the 'Stacked Chart Creator' toolbar
- The toolbar disappeared
- Closed Excel
- launched Excel and the toolbar was visible
You do not have the required permissions to view the files attached to this post.
Regards
Don
Don
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selectively Stacked Columns in a Chart
Don,
As you probably know, Excel 2007 and 2010 have done away with menus and toolbars in favor of the clunky "ribbon". Custom toolbars are still supported - they are displayed in the Add-Ins tab of the ribbon. Your add-in doesn't add a custom toolbar to the Add-Ins tab in my Excel 2007, though.
Did you attach the toolbar to the add-in? Or are you creating it on the fly?
As you probably know, Excel 2007 and 2010 have done away with menus and toolbars in favor of the clunky "ribbon". Custom toolbars are still supported - they are displayed in the Add-Ins tab of the ribbon. Your add-in doesn't add a custom toolbar to the Add-Ins tab in my Excel 2007, though.
Did you attach the toolbar to the add-in? Or are you creating it on the fly?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
Hi HansHansV wrote:Did you attach the toolbar to the add-in? Or are you creating it on the fly?
The toolbar was attached to the Add-In.
Regards
Don
Don
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selectively Stacked Columns in a Chart
OK, it turned out that the toolbar was there, but it was invisible.
You should add some code to the ThisWorkbook module of your add-in:
The Workbook_BeforeClose event procedure deletes the toolbar, so that Excel is forced to get a fresh copy from the add-in each time it is started. This prevents problems that would occur if the add-in is moved to a different folder, or if you release a new version with a modified toolbar.
The Workbook_Open event procedure ensures that the toolbar is visible.
You should add some code to the ThisWorkbook module of your add-in:
Code: Select all
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Stacked Chart Creator").Delete
End Sub
Private Sub Workbook_Open()
Application.CommandBars("Stacked Chart Creator").Visible = True
End Sub
The Workbook_Open event procedure ensures that the toolbar is visible.
Last edited by HansV on 27 Nov 2010, 22:17, edited 2 times in total.
Reason: to correct errors.
Reason: to correct errors.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
Recommended code added.HansV wrote:OK, it turned out that the toolbar was there, but it was invisible.
You should add some code to ...
File in prior post being replaced.
Regards
Don
Don
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
I have discovered a bug in my code. I am removing all posted copies until it is repaired. Humblest apologies. I will be back.
Regards
Don
Don
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Selectively Stacked Columns in a Chart
Hello All
The attached file incorporates a cure for the bug which I encountered. If you feel ambitious, carry out a few tests of your own. Please advise me of any problems you might encounter. Feel free to distribute.
The attached file incorporates a cure for the bug which I encountered. If you feel ambitious, carry out a few tests of your own. Please advise me of any problems you might encounter. Feel free to distribute.
You do not have the required permissions to view the files attached to this post.
Regards
Don
Don
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selectively Stacked Columns in a Chart
Thanks, Don. I haven't found any problems in Excel 2007.
Best wishes,
Hans
Hans