Dynamic sizing of a chart object

[Guest]

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

 

[Jon Peltier]

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/
_______

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

That's fixed it, thanks Tom