Chart created with a macro changes location according to screen resolution.

  • Thread starter Thread starter help_wanted
  • Start date Start date
H

help_wanted

I have a macro that creates a chart aligned with a set of cells. I
another user runs this macro with a different screen resolution th
chart covers a different area of the spreadsheet partially covering th
data. Also if the other users have additional or less toolbars, th
location of the chart is offset. Is there a way to assign the locatio
of a chart to cells and not screen location.

Thanks as always
 
Use something along the lines of:

Sub testIt()
With ActiveSheet.ChartObjects(1)
.Left = Cells(3, 3).Left
.Top = Cells(3, 3).Top
.Height = 200
.Width = 300
End With
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
You can adjust the code to do this automatically. A recorded macro will
make a chart sheet first, then change its location to a sheet. When it
moves the chart, it sticks it in the middle of the active window (active
pane if you have split panes activated), about half the width and height
of the window's usable height.

Instead of this two step process using Charts.Add, use this:

ActiveSheet.ChartObjects.Add left, top, width, height

where left, top, width, height are dimensions and positions in points.

There's more about VBA and charting here:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

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