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

  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
'at this point the Chart Object has become a ChartObject Object
  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

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...

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

CODE

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!