Moving and Recording Object Positions

[primed]

Hi Guys,

Attached is a sample spreadsheet in which i am trying to achieve 2 things.

1) On the scenarios page i want to select a scenario by the UID number. After hitting the "Position Object" button the "Multiply (1 to 9)" objects on the display sheet will then move to the appropriate cell which is listed under results for the selected UID.

2) When i move the "Multiply (1 to 9)" objects on the display sheet then select the "Record Positions" i want the new cell reference for each object to be recorded/updated in the results columns on the scenarios sheet.

Thanks in advance for your help.

Primed

[mikerickson]

Perhaps this. Note that all the shapes have been changed to Do Not Move or Size with cells.

[primed]

Hi Mike,

Almost perfect.
With the record button, after i select the button the "X" that has been repositioned manually by myself will automatically move again.
Any ideas?

Thanks for the quick response.

Primed

[mikerickson]

I think this might work.
I put a combo box on sheet DISPLAY, linked to SCENARIOS!D2, so that both those indicators will be synched. This uses the named ranges UIDList and chosenUID.

Changing either the combobox or SCENARIOS!D2 will change the position of the shapes.
They can then be moved and the Record Positions button will record the cells where the shapes are.

[primed]

Hi Mike,

Almost there. The record button doesn't put the correct cell reference in the correct column on the scenarios page. ie Multiply 1 should update column G, Multiply 2 update column H and so on.

Sorry i probably didn't make that clear enough in the first instance.

Thanks
Primed

[mikerickson]

remove the .Sort line in the RecordPositions routine.

[primed]

Thanks Mike, worked a treat.

[primed]

Hi Mike,

After some further testing the object/s that are relocated moves after the record button is selected. The other strange thing is if i then zoom out slightly and keep pushing record all the objects will end up walking until they reach cell A1.

Any ideas?

Thanks
Primed

[mikerickson]

Since you are using cell location, rather than .Left & .Top, to specify postition, the RecordPosition macro
1) Identifies the Shape's TopLeftCell
2) makes the .Top and .Left of the cell and the shape match
3) records the cell

If the top left corner of the shape is in the center of the cell, the routine will snap the shape to the cell grid before recording the position. This could be changed but recording a shape's location by storing the .Left and .Top of the shape rather than it's TopLeftCell.

I'm not sure what the zoom issue is. Perhaps if the shape were set to Don't Move Or ReSize with Cells.

[primed]

Hi Mike,

Checked the properties of the shapes and they were already set to "Don't Move or resize with cells". I then changed a couple of the shapes to "Move & Resize with Cells" as a test and that did fix the zoom issue.

In regards to the shapes repositioning after pushing record. I can now see after i hit record the object snaps to the grid. This is ok, however if i then hit the position button the shape will move slightly and not be snapped to the grid.If i hit record again the shape will then snap to the next cell over. On my full spreadsheet i have the cell size alot smaller and it is very evident then. Is it possible there is an error in the translation from topleftcell to .top &.left.

If we are recording the shapes topleftcell could we change the position button to position the shapes by topleftcell rather then .top & .left? I kind of understand the logic but its a little out of my league.

Primed

[mikerickson]

Try this, it doesn't have the snap to grid.

[primed]

Hi Mick,

I thought we had it. Copy the record button onto the scenario page for quicker testing. Enter the same cell references in a few of the rows. Click position and record a few time for the different scenarios then you will see the cell results change.It seems to only change once. They obviously shouldn't change because we haven't shifted any objects.

The other check, i went to a scenario hit the position button then moved one object. Hit the record button and check the results. There is a number of cells results for that scenario that change.

Primed