Shapes move in excel upon saving and reopening

[Weathertuck]

Greetings,

BACKGROUND: I'm using a modified version of a choropleth map macro (http://www.clearlyandsimply.com/clea...ith-excel.html), with the goal of creating an automatic map/graphic generator for values for work publications. So, precision is key. I've uploaded a new set of shape objects and have been modifying them so we have a higher definition map than the one provided in the base worksheet, and am currently in the process of renaming the objects and groups so they colorize correctly. So far, doing okay.

PROBLEM: Precision is key. The shapes move randomly so that countries aren't in the right place anymore whenever I save, close, and reopen the file. Not good since this has to be saved, shared, reopened repeatedly until the end of time and is a tool for non-computer savvy folks who won't take time to reposition the countries. Nothing changes, cells aren't resized. See the picture below for reference to the problem. More I save and reopen, or transfer via email, the more these things migrate. Tried across multiple computers, excel versions, etc.

Untitled.jpg


I'VE TRIED: Researching this thoroughly. Most suggestions are to adjust the properties of the shapes, to not move or resize, or to lock the objects. I've tried this, no solution. I've seen several similar threads, but no comments on if it worked or not or followup. It's not working for me. Tried adjusting objects to snap to grid or page, still not working. Looking through the code, it doesn't seem as though anything is changing the cells. So, I'm perplexed.

Any suggestions? Sorry if putting this in the VBA section was the wrong spot, but I've looked through the general sections and all the suggestions to related issues didn't seem to be working. Worksheet is attached. Thanks in advance!

[Green Crocodile]

Try grouping the shapes together to see if that helps. Select each shape, right click, group.

After locking the shapes have you tried protecting the worksheet before save?

If the above don't work the only other way I can think of is to have a VBA code to re-position the shapes every time the workbook opens.

Shapes not staying still is a common problem for many in Excel.

When you reopen the file are the shapes always off in the same positions or is it in a random position every time?

[Weathertuck]

Thanks for the reply!

I've tried locking the workbook before closing, it lessens the number of shapes that migrate, but doesn't completely resolve the problem. I've tried grouping, but as some of the shape objects are already grouped, i.e. the bahamas, if you do a massive grouping then the macro does't color the sub-group. Probably something to do with the new name of the group vs. the name of the sub-group. This won't work because the object names are what is called upon to color them, so if the group name supersedes the subgroup name, it doesn't seem to work. And it definitely seems to be random. More testing is needed to confirm that.

Would you happen to have an example of positioning script for objects? If I could find some way to set an absolute position on opening, that should work. It'll be time consuming, but I'm willing to do it. What would be incredibly helpful is figuring out their current position. Any thoughts? Thanks again!

[Green Crocodile]

Setting up the VBA to reposition the shapes when workbook is opened I think is well worth the time. You know you will be safe. See these two links. One to reposition shapes and the other to find the current coordinates.

REPOSITION:
http://www.mrexcel.com/forum/excel-q...ordinates.html

Please Login or Register  to view this content.

FIND COORDINATES:
http://www.ozgrid.com/forum/showthread.php?t=98456

Please Login or Register  to view this content.

[nsmdr]

Hi,
I've had the same trouble and searched many forums online and tried all the suggestions like selecting the checkbox for "locked" and "Don't move or size with cells" under properties. However none of these worked with Excel in Office 365. I didn't have this problem in earlier versions of Excel. After almost 2 weeks of breaking my head over this, I finally found a solution - by using VBA to zoom in and out of the sheet that had the shapes in when you open the workbook. Insert the code below to 'ThisWorkbook'

Please Login or Register  to view this content.

[scottiex]

Further to GC's post.

below will record the image positions on close and then reposition them on open.

although hopefully nsmdr's simple solution helps you.

Please Login or Register  to view this content.