Chart Management with Excel VBA

Now imagine that, as a manager of the company “ABC”, you need to create an annual revenue report, building charts for revenue only for January, for January and February, from January to March, and so on — a total of 12 charts (see file 5-ChartManagement.xlsm on the CD).

Of course, you could create all these charts manually, or you could manage with just one. In this case, you need a tool that allows entering a specified time interval, and the chart should automatically adjust accordingly. Here, controls come in handy — either a ListBox or a ComboBox.

Perform the following steps:

1. In cells A1 and B1, enter the column headers Month and Revenue, respectively.
2. In the range A2:A13, enter the month names, using Excel’s AutoFill function.
3. In the range B2:B13, enter the company’s revenues.
4. Create a chart based on these two ranges (A2:A13 and B2:B13): select the range A2:B13, go to the Insert tab, in the Charts group click the Column Chart dropdown, and choose the desired chart type from the collection.
5. Place a ComboBox control on the worksheet to allow selecting a time interval: go to the Developer tab, in the Controls group click Insert, and in the ActiveX Controls section, select ComboBox. Then, draw the control on the worksheet to the desired size.
6. In the Properties window for the ComboBox, set the ListFillRange property to A2:A13. This fills the list with data from the specified range.
7. Double-click the ComboBox to open the worksheet module.
8. Enter the following VBA code into the worksheet module (Listing 1.8).
9. Go back to the worksheet and test the ComboBox to quickly build charts of ABC’s revenues.

Chart Management

Private Sub ComboBox1_Change()
    Dim r As Integer
    ActiveSheet.ChartObjects(1).Activate
    r = ComboBox1.ListIndex + 2
    With ActiveChart
        .SetSourceData Source:=Sheets(1).Range(Cells(2, 2), Cells(r, 2)), PlotBy:=xlColumns
        .SeriesCollection(1).XValues = Sheets(1).Range(Cells(2, 1), Cells(r, 1))
    End With
End Sub

This code dynamically updates the chart based on the month selected in the ComboBox, allowing a single chart to display revenue for any period from January to the selected month.