Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Charts & VBA

VBA How To

Charts & VBA

by  SkipVought  Posted    (Edited  )
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...
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top