How can I keep pictures from moving around in Excel 2003?

[timrod]

How can I keep pictures from moving around in Excel 2003? I can place them in such-and-such a place and then save the file. When I reopen, they've moved. How can I anchor them?

 

[JoeMo]

Here's an example of code that will place a picture ("Picture 1" for this example) so that the top, left corner is anchored on cell B5 on worksheet "Sheet1". You can adapt this to your situation. The code goes in a module for ThisWorkbook.

Private Sub Workbook_Open()
With Sheet1.Shapes("Picture 1")
    .Left = Range("B5").Left
    .Top = Range("B5").Top
End With
End Sub

After you adapt the code, press alt + F11 key. Find your workbook in the VBE projects window, double-click ThisWorkbook icon and paste the code in the white space to the right of the projects window. Close the VBE window. Save the file (SaveAs a macro-enabled workbook if you are using Excel 2007 or later; .xlsm extension). Close the workbook and re-open it. The picture(s) should now be where you want them.

 

[JTBorton]

timrod,

All versions of Excel seem to have immense trouble keeping track of the correct locations of objects when there are numerous objects on a page, and most especially when there are several small objects combined to form a diagram or picture. I run into this problem often when I am trying to build process flow diagrams for my mass and energy balances at work. One possible way to fix this is to form your shapes into the picture or diagram you desire, group them together into one object, then copy and paste to Microsoft Paint. Crop the image, add whatever additional cleanup, drawing & markup you want to add, then copy and paste back to excel as an image instead of several grouped shapes. The downside is that once you do this there is no editing the images, so you'll have to take that into consideration. If you are completely done with your layout then go for it, or simply do it to sections that are done to avoid having to spend an hour readjusting them.