Move "Object" based on Cell Value?

Question

Excel 2010 ...

Range K4:AN50 ... Within each Row I have 3 Grouped Objects (each object the same size as a cell ... like a 3 car train) ...

Is it possible to move this Group of Objects (Right/Left within the Row) based on a Value within a Cell ... I would like the Right most object to move to a cell within its Row based on a Cell Value also within its Row ... Can this be done???  (KISS is good)

Then if possible ... I would like the Cells Value to appear within the Object ... & other values to appear in the trailing 2 Objects depending on if this can even be done & how complicated a formula might need to be within an Object??? ... And please note that I have no clue how complicated a formula can be within an object ... :)

I am smart enough to know I only scratch the surfaces regarding Excel capabilities so I am turning to this board & those of you that are intimate with Excel for guidance ...

Thanks ... Kha

Answer 1

Have a look here

http://www.youtube.com/watch?v=6fh5yBE6Moo

Andreas.

Answer 2

Andreas ... (Good morning)

Thank you for the response ... However, I know absolutely nothing regarding VBA ... I record Macros only & occassionally do some creative cut/paste after doing so ... Other than this I have no clue ...

So any further guidance is certainly welcome ... & if I can cut/paste the VBA you provide & make a few edits to get it to work ... Perfect ... but I will need further guidance ...

Thanks ... Kha

Answer 3

However, now I am curious to know if it is even possible to move an Object to a cell within a Range that contains a specific Value within a Cell???

Can you move the object with the mouse? Yes. Then you can move the object with VBA too.

With VBA, find the cell with value using RANGE.FIND, get the object from the Shapes collection of the sheet, then write the Top and Left properties of the Range object to the Shape object.

Andreas.

Sub Test()

  Dim R As Range

  Set R = Range("C3:F10")

  With ActiveSheet.Shapes("Rectangle 1")

    .Top = R.Top

    .Left = R.Left

    'Resize

    '.Width = R.Width

    '.Height = R.Height

  End With

End Sub

Answer 4

I can not upload file as data is Company Private ... However, perhaps I can simplify the issue ...

Range K4:K50 ...

Contains Formulas ... & Objects (Cell size) cover Cells K4, L4, M4 (note:  Objects are Grouped)

Above Scenario Repeats for each Row in Range K4:AN50

I would like to move the Objects (Left/Right) within their respective Row Range based on a value found either within a cell where I would like the "Lead" Object to be ... or perhaps even a value within the Object itself if any of this is even possible???

I did not originally return to the board because I went away from Objects & turned to Conditional Formatting of the Cells to obtain similar results ... However, now I am curious to know if it is even possible to move an Object to a cell within a Range that contains a specific Value within a Cell???

Thanks ... Kha

Answer 5

That depends on the layout of the file.

Please make an example file, upload it on an online file hoster like www.dropbox.com and post the download link here.

Andreas.