VBA to select a cell relative to a chart

[reece1984]

Hi All,

I have inserted macros to zoom to a chart when the chart is selected and then zoom out when it is selected again using an IF formula. The problem is that when trying to selecting the chart for the second time, it doesnt register because the chart is already selected. So the only way to select it and therefore zoom out again is to select somewhere else and then select the chart again.

The solutions that I have in mind, but do not know how to do them is either to have the macro finish by selecting a cell that is relative to the chart. Meaning that it can be selected again without zooming away.

Or have the macro end by selecting a regular cell such as A1 but not have the view shift to that newly selected cell.

Can anybody please help me implement one of these options?

Thanks very much.

J

 

[Domenic]

To select the cell that lies under the upper-left corner of the chart, try...

ActiveSheet.ChartObjects("Chart 1").TopLeftCell.Select

 

[reece1984]

Domenic,

Thanks very much, that's perfect.

I've now realised that the same functionality with other objects would be useful. Could you tell me how to select a cell relative to another object such as a button?

I've tried this but it doesnt seem to work.

'ActiveSheet.Shapes.Range(Array("button_1")).TopLeftCell.Select

thanks

J

 

[Domenic]

That's because the Range property of the Shapes collection returns a ShapeRange, and TopLeftCell is not a property of a ShapeRange. Instead, use the Item method of the Shapes collection...

ActiveSheet.Shapes.Item("Button_1").TopLeftCell.Select

or

ActiveSheet.Shapes("Button_1").TopLeftCell.Select

 

[reece1984]

Domenic, Thanks very much. I will mark this thread as closed.

 

[Domenic]

You're very welcome!