Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel 2010 vba AddChart.Chart vs ChartObjects.Add ChartWizard

    I was trying out creating a chart with vba using chartwizard. I cut and pasted some formatting code that I've used previously, but I get the following error:

    "Object doesn't support this property or method"

    at .ChartArea.Select.

    I've tried several ways to reference the chart object and I seem to be missing something in the concept and implementation for the creation with chartwizard.

    The code that fails follows and I've attached a worksheet with both sets of code and data. The sub lenfreq() runs and cust() gets the error.

    Code:
    Sub cust()
    Dim charttemp As ChartObject
    Dim ch As ChartObject
    Dim chartname As Variant
    Dim chartindex As Variant
    Set charttemp = Worksheets("sheet1").ChartObjects.Add(175, 30, 600, 375)
    charttemp.Chart.ChartWizard Source:=Worksheets("sheet1").Range("g2:i41"), _
        gallery:=xlColumnClustered, HasLegend:=False, _
        Title:="Length Frequency Distribution of all Sizes by Month" & Chr(10) & Cells(2, 2) & Chr(10) & "(" & Cells(2, 3) & ")", _
        CategoryTitle:="Range of Lengths (mm) by Month", _
        Valuetitle:="Frequency of Lengths"
    chartname = charttemp.Name
    
    With charttemp
            ' get object error here (Object doesn't support this property or method)
            .ChartArea.Select         
            'title1len = Cells(1, 6).Characters.Count
            With charttemp.ChartTitle
                .Font.Size = 12
                .Characters(1, 30).Font.Size = 18   'title1len
                
            End With
            With .Axes(xlValue)
                .HasMajorGridlines = False
                .HasTitle = True
                With .AxisTitle
                    '.Text = Cells(1, 3)
                    .Font.Name = "calibri"
                    .Font.Size = 12
                    .Font.Bold = True
                End With
                    
            End With
            With .Axes(xlCategory)
                .HasMajorGridlines = False
                .HasTitle = True
                With .AxisTitle
                    '.Text = Cells(1, 2)
                    .Font.Name = "calibri"
                    .Font.Size = 12
                    .Font.Bold = True
                End With
    
            End With
    End With
    
    End Sub
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Required an activate, which I had already tried and the one thing I did not try before posting, working with the ActiveChart. Another error in the code above is the missing .HasTitle. Code snip follows.

    Code:
    charttemp.Activate
    With ActiveChart
           'title1len = Cells(1, 6).Characters.Count
            .HasTitle = True
            With ActiveChart.ChartTitle
    .......................................
    So, why doesn't do something with this object variable work in this case? Ie why does "with charttemp" work when using Chartobjects.Add and does not work when a chart is added using the ChartWizard?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Charttemp is a ChartObject, which contains a Chart. It is not itself a chart. So you need something like:
    Code:
    With charttemp.Chart
            ' get object error here (Object doesn't support this property or method)
            .ChartArea.Select
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Jul 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In Excel 2010 charttemp object is NOT a ChartObject but a shape object containing a Chart

    In Excel 2003 ChartObjects.Add returns a ChartObject
    In Excel 2010 ChartObjects.Add returns a Shape

    dim shp as Shape
    dim chtOBJ as chartobject
    set shp = Activeworksheet.Chartobjects.Add

    set chtobj = shp.chart.parent ' if require to reference chartobject

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No it doesn't - it still returns a ChartObject. Shapes.Addchart would return a shape though.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    Jul 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are right Rory,

    Different object type is returned by Chartobject.Duplicate , not chartobjects.add where same type is returned on Excel 2003 and 2010

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •