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

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
 
T

Tushar Mehta

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
 
J

Jon Peltier

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top