Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic sizing of a chart object
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").ChartO bjects.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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic sizing of a chart object
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic sizing of a chart object: Correction
re my previous post, if the period is not inserted, the error message
pertains to the following line, ie .ChartType = xlLine |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic sizing of a chart object: Correction
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" wrote in message ... re my previous post, if the period is not inserted, the error message pertains to the following line, ie .ChartType = xlLine |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic sizing of a chart object: Correction
That's fixed it, thanks Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart sizing | Excel Discussion (Misc queries) | |||
Chart Sizing | Charts and Charting in Excel | |||
chart sizing | Charts and Charting in Excel | |||
sizing a chart | Charts and Charting in Excel | |||
Dynamic Chart Object | Excel Discussion (Misc queries) |