ChartObject in Excel graph (Office xp, win2000)

Topic

New Reply

I’m writing a macro to make a Bar Chart.

Range(“A6:C19”).select
charts.add
ActiveChart.ChartType = xlBarClustered
…

I can’t figure out how to size this embedded chart. I’ve tried set eChart = ActiveSheet.ChartObjects(1).chart
eChart.Width = 100

but that seems to be the wrong syntax. What am I doing wrong? Thank you

Reply | Quote

Replies

You need to treat the Chart as a member of the Shapes collection. The following will increase the width and height by a factor of 1.5

    With ActiveSheet.Shapes("Chart 1")
        .ScaleWidth 1.5, msoFalse
        .ScaleHeight 1.5, msoFalse
    End With

or the Following will apply specified dimensions (points, I think)

    With ActiveSheet.Shapes("Chart 1")
        .Height = 150
        .Width = 250
    End With

Andrew C

Reply | Quote

If you create the embedded chart directly, then you can specify the left, top, width, and height directly. Also remember that all of the AddXXX methods return an object, so use a set statement or a with block:

Option Explicit
Sub Macro2()
Dim co As ChartObject
    Set co = ActiveSheet.ChartObjects.Add(10, 100, 500, 200)
    co.Chart.ChartType = xlBarClustered
    co.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B3")
End Sub

Sub Macro3()
    With ActiveSheet.ChartObjects.Add(10, 100, 500, 200)
        .Chart.ChartType = xlBarClustered
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B3")
    End With
End Sub

Reply | Quote

Thank you so much!

Reply | Quote