Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 3

Excel VBA has provided some very useful and powerful features to manipulate data sources as well as the appearance of embedded charts. As a matter of fact almost EVERY single aspect of a chart can be modified by Macros. The example I show here should give you a taste of such functionalities.

First things first, we need a name for the chart we intend to control so that we may effectively reference the chart in our codes. There are two ways to locate and change the name of an embedded chart. Click on the chart, and look in one of the following two places:

Enter a customized name (if you choose) into one of the two textboxes outlined above and press enter. Make sure that each chart has its own unique name.

Now let's take a look at the following codes we used to chart "Monthly Regional Sales" in the given example. Once again, you can download the example file here.

Key grammars of the codes include:

1), Sheets("Dashboard").ChartObjects("Chart1"), is used to reference an embedded chart named "Chart1" in worksheet named "Dashboard". Please note that to reference chart elements such as chart titles, legends and data series, we should use:

Sheets("Dashboard").ChartObjects("Chart1").Chart.ChartTitle

Sheets("Dashboard").ChartObjects("Chart1").Chart.Legend

Sheets("Dashboard").ChartObjects("Chart1").Chart.SeriesCollection

2), Before we redraw the chart we need to delete the current content in the chart - i.e. all data series (circled by red pen) and (potentially) trendlines (circled by blue pen). Now that if there is currently no trendline in the chart, the attempt to delete it would trigger an error message. Use "On Error Resume Next" to ignore all such errors.

3), The line to determine the chart type (i.e. if it's a line chart or a column chart) is underlined by black pen: .Chart.ChartType = xlLine. A good source for all chart types can be found here.

4), Codes circled by Magenta pen detail how to add a data series as part of a line chart, as well as how to customize the look of the data series. Most properties are self-explanatory (for example xValue determines the x-Axis data range, .Border.Color determines the line color, etc). One property, .Points(.Points.Count), references to the last (right-most) data point of the data series. To reference the first data point, use .Points(1). To reference all data points, set up a loop between 1 and .Points.Count.

5), Codes detailing how the axes and legend would appear are pretty easy to understand too. One thing I would like to point out is .Chart.Axes(xlValue) refers to the y-Axis while .Chart.Axes(xlCategory) refers to the x-Axis.