At times I need to present financial models to Committees in a format that allows the group to interactively view the results of changes made to the model. The ability to name, hide, and show multiple charts via VBA is great for these types of presentations. Ideally, I like to stack all of the charts to be presented one on top of another. Stacking the charts prevents me from having to skip around the worksheet to find the chart as will as insures that any chart that I choose is positioned exactly where I want it to be on the overhead viewer. I then show the selected chart and hide the others via command buttons, option buttons, or combo boxes.

As a example, I first name all of the charts in the model using the following code (you must select the chart first and then run the macro to name it):

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

I then size one or more of the charts to the same outside dimensions and position on the worksheet. To size and position a single chart, I use this code:

Sub SizeAndPositionSingleChart()
    Dim Shp As Shape
    Set Shp = Worksheets("Sheet1").Shapes("Cht1")
    With Shp
        .Height = 168.75
        .Width = 286.5
        .Top = 18
        .Left = 50
    End With
End Sub

To size all of the charts on the worksheet to the same outside dimensions and position, I use the following code:

Sub SizeAndPositionAllCharts()
    Dim Cht As ChartObject
    For Each Cht In ActiveSheet.ChartObjects
        Cht.Height = 168.75
        Cht.Width = 286.5
        Cht.Top = 18
        Cht.Left = 10
    Next Cht
End Sub

In this example, assume the model contains two charts stacked one on top of the other. Using the NameChart macro above, one chart has been named "Cht1" and the second "Cht2". A macro loaded into a regular module similar to the one below will show Cht1 while hiding Cht2.

Sub ShowChart1()
    Worksheets("Sheet1").Shapes("Cht1").Visible = True
    Worksheets("Sheet1").Shapes("Cht2").Visible = False
End Sub

The following macro will show Cht2 and hide Cht1:

Sub ShowChart2()
    Worksheets("Sheet1").Shapes("Cht2").Visible = True
    Worksheets("Sheet1").Shapes("Cht1").Visible = False
End Sub

Assuming the charts are on "Sheet1", showing and hide the charts using command buttons can be accomplished by loading the following code into the sheet module for "Sheet1". To show Cht1 and hide Cht2, use the following:

Private Sub CommandButton1_Click()
    Shapes("Cht1").Visible = True
    Shapes("Cht2").Visible = False
End Sub

To show Cht2 and hide Cht1, use the following:

Private Sub CommandButton2_Click()
    Shapes("Cht1").Visible = False
    Shapes("Cht2").Visible = True
End Sub

A simple model showing Cht1 looks like this:

The same model showing Cht2 is below:

You could alternate between two charts with a single button:

Private Sub btnSwitchCharts_Click()
  Shapes(“Cht1”).Visible = Not Shapes(“Cht1”).Visible
  Shapes(“Cht2”).Visible = Not Shapes(“Cht1”).Visible
End Sub

In general, if the chart types aren’t too different, I prefer to accomplish this without VBA, instead using dynamic ranges, and hiding a series by changing its data range to a blank region of the worksheet.