Charts & VBA and Making Your Chart Interactive

Engineering Software general discussion VBA
, , ,
#1

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. (Charts at the same level as Worksheets)

  2. Worksheet.ChartObjects Collection of ChartObject Objects. These are Charts in a Worksheet Object. (Charts embedded in Worksheets)

When you use the Add Method to create a new Chart, it creates an Chart object. If you were to Macro Record the process of adding a 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 accordingly as in this example…

CODE

Sub AddChart()
  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
End Sub

The Interactive Chart

Let’s say that you have data in a table for multiple tests. The quick & dirty way is to create a chart for each test. 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 Control 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!