VBA to re-position shapes

[ExcelWhizzDotCom]

Hi all, can any VBA whizzes help me out?

I'm trying to programmatically maintain the position of autoshapes on my worksheet, one of the routines re-sizes column widths but I want to write into it something which remembers their position at the start of the routine and puts them back after the column widths are changed.

I've tried something simple:

    ActiveSheet.Shapes.SelectAll
    Selection.ShapeRange.IncrementLeft 10000

And then at the end move it back -10000. But the problem is if overall widths have reduced, the -10000 step takes everything too far to the left.

If anyone has a solution it'd be gratefully received!

Thanks
CW

 

[RickXL]

Hi,

Have you tried setting the "Don't move or size with cells" property on the format tab for each shape?

Is that what you are trying to do?

Regards,

 

[ExcelWhizzDotCom]

Perfect, thanks Rick!

I had tried all sorts with VB and it wasn't even needed. Thanks very much.

 

[RickXL]

No problem.

There is so much buried in the way of options and settings in Excel I don't think anyone can keep track of all of them.

The skill lies in knowing when it is worth looking.

Regards,