MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

error using secondary axis in excel chart (VBA)

  Asked By: Clifton    Date: Oct 16    Category: MS Office    Views: 3599

I just recorded a macro for creating a "Line - Column on 2 axes" chart. But
when I run it, it gives me the following error "Method 'Axes' of objects
'Charts' failed".
I tried modified the code by defining ch1 as chartobject and then doing
set ch1 = activesheet.chartobjects(activesheet.chartobjects.count) and
with activechart by ch1.chart . It gives me the error " Unable to access
property 'chartobjects' of object worksheet'

Can anybody please help me?

Sub Macro9()
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A5:D12"), PlotBy _
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "performance"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "xxx"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "yyy"
End With
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(2).AxisGroup = 2
End Sub



3 Answers Found

Answer #1    Answered By: Jean Bell     Answered On: Oct 16

Four questions

1. What version of Excel are you using?
2. What references are set  (in the VBE look at tools/references and see what
is ticked)
3. Where is the sub. (This Worksheet, This Workbook, Module 1, etc.)
4. Are you running the macro  from an open workbook or from the VBE; Is there
a workbook oppen at the time?

Answer #2    Answered By: Dominic Murphy     Answered On: Oct 16

Answers to ur questions:

1. excel  2003
2. References :
VBA for applications excel 11.0 object  libraryOLE AutomationMicrosoft forms2.0
objects libraryMicrosoft ADO Ext. 2.8 for DDL and securityMicrosoft HTML Object
LibraryOutlook object libraryPowerpoint Object library3. Sub is in Module1
4. No, there is no other workbook open.

Answer #3    Answered By: Jeffrey Washington     Answered On: Oct 16

I suggest you remove the references to the following (unless you specifically
need them In this module)

Microsoft forms2.0 objects library
Microsoft ADO Ext. 2.8 for DDL and security
Microsoft HTML object  Library
Outlook object library
Powerpoint Object library

You might need to add

Microsoft Office 11.0 Object Library

I think the problem might be that one of the other libraries also has a chart
object but it does not have the properties/methods you are using.

If you do need the references you might try specifying the chart  in the DIM as

Didn't find what you were looking for? Find more on error using secondary axis in excel chart (VBA) Or get search suggestion and latest updates.