What VBA Stmt Will Tell Me What a Chart's PlotNo Is?

  • Thread starter Thread starter SidBord
  • Start date Start date
S

SidBord

I have a regular worksheet (not a chart sheet) that has 10
charts. I have written a macro that will allow the user to
change the row limits of the SERIES formula. The macro
works, but critical to making it work is knowing what the
"PlotNo" (chart number on the sheet) is. What is the VBA
statement that will tell me what a chart's Plot number is?
He also needs to specify the SERIES number, but he can
determine that by selecting the graph he wants to change.
The two main statements I'm using are:
Set Cht = ActiveSheet.ChartObjects(PlotNo).Chart
Series = Cht.SeriesCollection(SeriesNo).Formula
 
ActiveChart.Parent.Index returns the chartobject's index.
Selection.Name tells you the name of the selected series, which is as
good as the series index, unless you have multiple series with the same
name.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
If the user will indicate which series to change by selecting it, why
not use that information?

Sub testIt()
Dim aChart As Chart, aSeries As Series
If Not (TypeOf Selection Is Series) Then Exit Sub
Set aSeries = Selection
Set aChart = aSeries.Parent.Parent
MsgBox aChart.Name
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top