Moving a shape without explicitely naming the shape

ss123

New Member
Joined
Mar 4, 2014
Messages
21
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
With Sheets(1)
   With .Shapes(.Cells(3, 10).Value)
      .Top = 123
      .Fill.ForeColor.RGB = 5678901
   End With
End With
 

ss123

New Member
Joined
Mar 4, 2014
Messages
21
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
That code works in a normal module. Did you copy the whole code?
 

ss123

New Member
Joined
Mar 4, 2014
Messages
21

ADVERTISEMENT

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

ss123

New Member
Joined
Mar 4, 2014
Messages
21
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the name of the sheet with the shapes?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
With Sheets("Sheet1")
   With .Shapes(.Cells(3, 10).Value)
      .Top = 123
      .Fill.ForeColor.RGB = 5678901
   End With
End With
 

Forum statistics

Threads
1,148,252
Messages
5,745,674
Members
423,967
Latest member
malayaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top