Dynamic sizing of a chart object

My macro creates a chart, positions its top left corner appropriately on the
worksheet and uses the following command to size the height of the chart to
fit across 8 rows of the sheet:
ActiveSheet.Shapes("MyChart").ScaleHeight 0.8, msoFalse,
msoScaleFromTopLeft

My problem is that if I change the view/zoom setting, the object height does
not adjust accordingly. A scale height of 0.8 is correct when the zoom
setting is 100% and incorrect for every other setting. How do I fix this
please?
TIA .. Greg

I find it much more reliable not to add a chart with Charts.Add, which
creates a chart sheet first, then moves it to the worksheet. I use this
syntax:

Workbooks("Book1.xls").Worksheets("Sheet1").ChartObjects.Add _
Left, Top, Width, Height

where Left, Top, etc., are positions in points. If you want to place the
chart exactly on a range of cells, say, B3:F15, use something like this:

With Workbooks("Book1.xls").Worksheets("Sheet1")
.ChartObjects.Add .Range("B3").Left, .Range("B3").Top, _
.Range("B3:F15").Width, .Range("B3:F15").Width
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"gvm" <[email protected]> wrote in message
news:[email protected]...
> My macro creates a chart, positions its top left corner appropriately on
> the
> worksheet and uses the following command to size the height of the chart
> to
> fit across 8 rows of the sheet:
> ActiveSheet.Shapes("MyChart").ScaleHeight 0.8, msoFalse,
> msoScaleFromTopLeft
>
> My problem is that if I change the view/zoom setting, the object height
> does
> not adjust accordingly. A scale height of 0.8 is correct when the zoom
> setting is 100% and incorrect for every other setting. How do I fix this
> please?
> TIA .. Greg

Thanks Jon.
I have an issue integrating your new statement in place of the old
Charts.Add statement. This is an excerpt of the code:
With Worksheets("Traffic model")
.ChartObjects.Add .Range("G1").Left, .Range("G1").Top,
..Range("G1:k11").Width, .Range("G1:k11").Height
Set YRng = .Range(.Cells(initial_blank_count - 1, initial_column +
6), .Cells(LastRow, initial_column + 6))
Set XRng = .Range(.Cells(initial_blank_count - 1, initial_column),
..Cells(LastRow, initial_column))
With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=YRng, PlotBy:=xlColumns

Debug highlights the error line to be "With ActiveChart". The error is
"Object variable or with block variable not set". If I insert a period in
front of ActiveChart, I receive the error "Object doesn't support this
property or method".

Can you advise the problem please, TIA ... Greg

re my previous post, if the period is not inserted, the error message
pertains to the following line, ie .ChartType = xlLine

Dim cObj as ChartObject
With Worksheets("Traffic model")
set cObj = .ChartObjects.Add( .Range("G1").Left, _
.Range("G1").Top, .Range("G1:k11").Width, _
.Range("G1:k11").Height)

Set YRng = .Range(.Cells(initial_blank_count - 1, _
initial_column + 6), .Cells(LastRow, initial_column + 6))
Set XRng = .Range(.Cells(initial_blank_count - 1, _
initial_column), .Cells(LastRow, initial_column))
End With
With cObj.Chart
.ChartType = xlLine
.SetSourceData Source:=YRng, PlotBy:=xlColumns

--
Regards,
Tom Ogilvy


"gvm" <[email protected]> wrote in message
news:[email protected]...
> re my previous post, if the period is not inserted, the error message
> pertains to the following line, ie .ChartType = xlLine
>

That's fixed it, thanks Tom