Changing the Chart Type with Excel VBA

Now let’s modify the previous example and add another list on the worksheet so that the user can control both the chart type and the legend display.

• On the Vedomost worksheet, create a second list. In the Properties window, set its Name property to DType.
• In the worksheet module Vedomost, additionally enter the procedure FillDType. In the Workbook_Open event procedure, add a call to FillDType as well.

Controlling the chart type and legend. ThisWorkbook module

Private Sub Workbook_Open()
    DeleteCharts
    ChartBuilder
    FilllstCategory
    FillDType
End Sub

Private Sub FillDType()
    Dim tb(6, 1) As Variant
    tb(0, 0) = "Column":     tb(0, 1) = xlColumnClustered
    tb(1, 0) = "Line":       tb(1, 1) = xlLine
    tb(2, 0) = "Pie":        tb(2, 1) = xlPie
    tb(3, 0) = "Doughnut":   tb(3, 1) = xlDoughnut
    tb(4, 0) = "Area":       tb(4, 1) = xlArea
    tb(5, 0) = "Bar":        tb(5, 1) = xlBarClustered
    tb(6, 0) = "Cone":       tb(6, 1) = xlConeColStacked
    With Worksheets("Vedomost").DType
        .ColumnCount = 2
        .TextColumn = 2
        .ColumnWidths = "70;0"
        .List = tb
        .ListIndex = 0
    End With
End Sub

• In the Vedomost worksheet module, enter the following event-handling procedure for the Click event of the list. This will rebuild the chart whenever a chart type is selected (Listing 6.4).

Selecting the chart type. Vedomost worksheet module

Private Sub DType_Click()
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.ChartType = DType.Text
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then
        ActiveChart.HasLegend = True
    Else
        ActiveChart.HasLegend = False
    End If
End Sub

Notes

• Two-column list design. Charts have both names (e.g., “Column”, “Pie”) and constants (e.g., xlColumnClustered, xlPie) that define chart types. The list should display only the chart type names, but the result of the selection must be the constant. To solve this, the code creates a two-column list:
  • the first column contains the names of the chart types,
  • the second column contains the constants defining the chart types.
    Since we don’t need to display the second column, its width is set to zero. However, because the list’s TextColumn property is set to 2, the selected item’s Text property returns the constant (second column), not the name.
• Legend necessity. Depending on the chart type, the legend may be necessary or redundant. For example:
  • For a column chart, the legend is unnecessary in the built application because the chart already includes clear labels.
  • For pie or doughnut charts, the legend is indispensable.
    Therefore, in the DType_Click procedure, the legend is either shown or hidden depending on the selected chart type.