Logo 
Search:

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: 3678
  

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
this:
set ch1 = activesheet.chartobjects(activesheet.chartobjects.count) and
replacing
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()
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A5:D12"), PlotBy _
:=xlColumns
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).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.ChartArea.Select
End Sub

Share: 

 

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

 
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.




Tagged: