Charting and VBA

[baz0912]

Hi - I'm trying to set up buttons for my graphs that I can click on when I want a particular graph to come up. At the moment I have each graph on its own sheet but I would like the graph to appear on the data sheet as an object when you click the button.

Any thoughts on the best way of acheiving this?

Many Thanks
Harry

[Andy Pope]

You could start by using the macro recorder.

Perform the task of changing the location of 1 of your charts sheets.

I guess you will also need to record the code for converting a chart object back to a chart sheet.

You may find changing location alters the layout of the chart as font size may alter, if Auto scaling, when switching between sheet and object.

[baz0912]

Thanks, I've tried that and it seems to come up with an error.

I've got this code:

Worksheets("Sheet1").ChartObjects("Chart 1").Activate
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= "MyChart"

This will change the embedded chart, Chart 1 on Sheet 1 to a chart sheet named MyChart.

I want to do it the other way round, move a chart sheet to a sheet embedded. Shoyuld be quite straight forward, I imagine.

[Andy Pope]

Here you go.

It uses the buttons caption to locate, move and name charts.
Allow there is a named range for the chartobject to be displayed in.

Please Login or Register  to view this content.