Selectively Stacked Columns in a Chart

[Don Wells]

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.

[HansV]

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:

x404.png

Series OI is missing, and there is an unnamed series without data (it turns out to be column B which is empty).

[sdckapr]

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

[Don Wells]

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:

x404.png

Series OI is missing, and there is an unnamed series without data (it turns out to be column B which is empty).

Thank you for the feedback Hans.

• 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.

I will give some thought to any scenario which could cause your results.

[HansV]

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:

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

Check carefully whether this works in Excel 2003; it does in Excel 2007.

[Don Wells]

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 ...

Thank you Hans

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.

[HansV]

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.

[Don Wells]

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.

That just got added to my To-Do list

[Don Wells]

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.

Please see attached

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.

[HansV]

I don't see a custom toolbar in Excel 2007.

[Don Wells]

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

Note that the custom toolbar has its own Help button.

[HansV]

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?

[Don Wells]

Did you attach the toolbar to the add-in? Or are you creating it on the fly?

Hi Hans
    The toolbar was attached to the Add-In.

[HansV]

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:

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_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.

[Don Wells]

OK, it turned out that the toolbar was there, but it was invisible.

You should add some code to ...

Recommended code added.
File in prior post being replaced.

[Don Wells]

I have discovered a bug in my code. I am removing all posted copies until it is repaired. Humblest apologies. I will be back.

[Don Wells]

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.

[HansV]

Thanks, Don. I haven't found any problems in Excel 2007.