cellMatrix.net

Stacking Embedded Charts

A technique that I've found useful for presentations or working in small worksheet spaces involves stacking embedded charts on top of each other. Stacking the charts is done in such a fashion that only one chart is visible at a time. When a chart is needed, it is made visible by calling it to the top of the stack.

For example, start with four embedded charts on a worksheet named "charts":

First apply a name to each chart. This is not required but in my experience I've found it's easier to keep track of chart names as opposed to numbers. This is especially true if you're constantly adding and deleting charts. An easy way to name a chart is by activating it and then running the macro below.

Sub NameChart()
    ActiveChart.Parent.Name = "Cht01"
End Sub

I've named the charts in the screenshot "Cht01", "Cht02", "Cht03", and "Cht04".

Next, stack the charts one on top of another. Again, a macro makes this easy. The macro below sizes each chart to the size of the first chart (Cht01). It then positions each chart into cell C2.

Sub StackCharts()

    Dim ChtObj As ChartObject
    Dim myCht As ChartObject
    Dim Rng As Range

    Set myCht = Worksheets("Charts").ChartObjects("Cht01")
    Set Rng = Worksheets("Charts").Range("C2")
    
    For Each ChtObj In ActiveSheet.ChartObjects

        ChtObj.Top = Rng.Top
        ChtObj.Left = Rng.Left
        ChtObj.Height = myCht.Height
        ChtObj.Width = myCht.Width

    Next ChtObj

End Sub

At this point only one chart is visible. Finally, you can use controls to determine which chart is visible. In the screenshot below I've added an option button for each chart. Each option button contains code that makes the chart associated with it visible by bringing it to the top of the stack. All four option buttons are grouped together.

The worksheet showing a single stacked group of charts with option button controls now looks like this:

Code now has to be added to each option button to control the position in the stack for the chart that it is associated with. The code examples below need to be copied into the "charts" sheet module.

The code for the option button titled "Show Chart 1" is below:

Private Sub OptionButton1_Click()
    Dim ChtObj As ChartObject
    Set ChtObj = Worksheets("Charts").ChartObjects("Cht01")
    ChtObj.ShapeRange.ZOrder msoBringToFront
End Sub

The code for the option button titled "Show Chart 2" is below:

Private Sub OptionButton1_Click()
    Dim ChtObj As ChartObject
    Set ChtObj = Worksheets("Charts").ChartObjects("Cht02")
    ChtObj.ShapeRange.ZOrder msoBringToFront
End Sub

And so on for buttons three and four.

At this point the model is finished and the visibility of each chart is controlled by the option buttons.