Moving a shape without explicitely naming the shape

[ss123]

Hello,

Using the moderator, from this site (Fluff)'s help a couple days ago, a sequence of code was developed that showed when clicking on a cell the name of any shape that was currently located directly within that cell or astride of its borders. That worked great thanks to Fluff. But the reason I want the code that Fluff developed to run was that I wanted to move the shape that was within the cell in a prescribed direction based on the user's choice. To do so I only found one method which is the Activesheet.Shapes("Put Explicit Shape Name Here").Top and Activesheet.Shapes("Put Explicit Shape Name Here").Left. But I do not want to explicitly name my shape. I want to dynamically change the shape name based on the cell that the user clicked on. So it could be Shape_1 that would be in there or Shape_87652 or whatever. Obviously explicitly naming the shape is pointless and a not very automated way of running a program. In fact, I call it hard coding.

So I originally thought to create a string variable which I would make it equal to:
Chr(34) & Sheets(1).Cells(3,10).Value & Chr(34)
The reference of Sheets(1).Cells(3,10) is where I put the shape name that Fluff's procedure brought up. But of course, if I put a variable name in the parenthesis of the command Activesheet.Shapes() I will get an error that tells me that there is no such shape because it is looking for an explicitely defined name in quotes and not a variable. Yet a msgbox will show that the text value that I put into that string is in the exact form of a shape name (e.g. "Shape_1"). To me, any method that forces you to put in a hard coded name is not very good. The whole point about using code in Excel is to automate things not to create static and unchangeable code.

So the question: is there a method to move a shape within cells in Excel that does not need you to explicitly define that shape name and will allow a variable to be inserted into it instead?

Fluff's procedure was inserted into the sheet code as such (I modified it for my purposes):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Shp As Shape


For Each Shp In Me.Shapes
If Shp.TopLeftCell.Address = Target.Address Then
Sheets(1).Cells(3, 10).Value = Shp.Name
Sheets(1).Cells(2, 10).Value = Shp.Top
Sheets(1).Cells(1, 10).Value = Shp.Left

Exit For
End If
Next Shp

End Sub

I never asked Fluff about this second part of moving the shape because I thought it would be a cake-walk ---- obviously I was very wrong about that

The interface I set up in Excel includes a floating tool bar with direction arrows on it. The user clicks on the cell in which is contained the shape that must be moved, and then clicks on the directional arrow he/she wants to move the shape in. The code then moves the shape accordingly or it would do so if there were better shape movement controls other than the not so great Activesheet.Shapes method.

Pray tell me that there is an automated way to do what I am looking to do....? If not, I will drop the concept of using shapes in Excel.

Thanks,

ss123

 

[Fluff]

How about

With Sheets(1)
   With .Shapes(.Cells(3, 10).Value)
      .Top = 123
      .Fill.ForeColor.RGB = 5678901
   End With
End With

 

[ss123]

How about

With Sheets(1)
   With .Shapes(.Cells(3, 10).Value)
      .Top = 123
      .Fill.ForeColor.RGB = 5678901
   End With
End With

It gives me an error (Invalid or unqualified reference) and highlights the (.Cells portion of the With Shapes(.Cells(3,10).Value) command. I suspect that it cannot be in a normal module, correct? I have to put it in the Sheet code area?

 

[Fluff]

That code works in a normal module. Did you copy the whole code?

 

[ss123]

Actually, even if put it the sheet code area, it still gives me the error.

 

[ss123]

That code works in a normal module. Did you copy the whole code?

Everything but the RGB color, I did not need to change its color. But, just to see if it would make a difference, I did put in the color command a couple of minutes ago and got the following error: "The index into the specified collection is out of bounds." It then highlights the With .Shapes(.Cells(... line

 

[Fluff]

What is the name of the sheet with the shapes?

 

[ss123]

Sheet1

 

[Fluff]

Ok, how about

With Sheets("Sheet1")
   With .Shapes(.Cells(3, 10).Value)
      .Top = 123
      .Fill.ForeColor.RGB = 5678901
   End With
End With