Keep in mind that there are
2 primary collections related to charts in Excel...
1. Application.Charts Collection of
Chart objects. These are Chart Sheets in the Application object
2. Worksheet.ChartObjects Collection of
ChartObject objects. These are charts in a Worksheet object.
When you use the Add method to create a new chart, it creates an Application.Chart object. If you were to macro record the process of adding an chart, and then modify the code, you need to keep this in mind when the code refers to the ActiveChart.
At the point that you specify the
Location property, if the location is in a worksheet, the ActiveSheet now referrs to a ChartObject in that Worksheet.
The With...End With construct is useful in centering on an object for coding and processing efficiencies. So the Object needs to be recognized for what it is and and handled properly...
Code:
Dim oChart as Chart, oChartObj as ChartObject
[highlight yellow]
Set oChart = Charts.Add
With oChart
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
.SetSourceData Source:=Sheet1.Range( _
"D1:E3"), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="Sheet2"
End With[/highlight]
[b]'at this point the Chart Object has become a ChartObject Object[/b][highlight #FF99FF]
With ActiveSheet
Set oChartObj = .ChartObjects(.ChartObjects.Count)
End With
With oChartObj.Chart
.HasTitle = True
.ChartTitle.Characters.Text = "My Title"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "My Pri Axis"
End With[/highlight]
The Interactive Chart
Let's say that you have data for multiple managers. The quick & dirty way is to create a chart for each manager. But then the boss says, change the
fill in the blank. So you have to make the change in each chart and after several cycle of changes, you can never be sure that they're all formatted identically, PLUS you've spent TOO MUCH TIME on duplicated effort.
Unless there is a requirement to see ALL THE CHARTS AT ONCE, SIDE BY SIDE, there is no need to maintain multiple charts. Enter the INTERACTIVE CHART. In most cases your interactive chart can be controlled from a single drop down or combo box.
1. Set AutoFilter on your chart source data. Record a macro to select a value in the column you desire.
2. Create a list of unique selection values using either the
Advanced Filter - Copy to another location - Unique Values or MS Query via
Data/Get External Data. If you need the contol object ON THE CHART, then you'll need to use a FORMS control object opposed to a Control Toolbox object. The former has fewer attribute and consequently cannot appear as glitzy as the latter, but it can do the job. Assign the macro that you recorded, to the control object so that when you make a selection in the combo box, the macro will execute, changing the AutoFilter in your chart source data. By the way, your Chart Options must be set to
Plot visible cells only.
3. Modify the macro to substitute the selected data value from the combo box in the filter criteria.
And now you have an
Interactive Chart!
More to come...
