Faster way to populate shape names?

[gshock]

Hello all, I'm looking for a faster way to name shapes in an Excel document/worksheet. The application is vibration data, and I'm using triaxial accelerometers. Each location has three readings, so I used shapes to indicate the value of the reading in three directions, X, Y, Z. Using the fine logic I borrowed from this thread: Macro to fill shapes I am nearly complete with my reporting, because every aspect of the data can be automated. The only thing left is that the number of shapes is fixed. If I want more, or need more, they have to be added and renamed manually (Selection Pane). Is there a way to link the object's name to a cell reference?

Above is an example of the shapes I'm using. The pentagons represent vertical and axial, while the rectangle represents horizontal (coming out of the page). Suppose I want to add more shapes, but I don't want to spend time renaming each group, is there a way to automate this so that I can link the name (not the shape value/text) of each shape to a cell?

 

[jolivanes]

Shapes have a TopLeftCell. I don't quite grasp what you're after but have a look at that.
If you're looking at adding shapes in a particular range, can be cell, row and whatever, that's probably the way to go.
If you tell us what you need, not in a bookwork, it should be simple.
Your question could be that you want to add a shape below the last shape in Column A. Give us the height, width and someone will help.

 

[gshock]

jolivanes, thanks for reaching out. Let's start with the "Why". These shapes are used to indicate where limits have been exceeded. Each data point is unique, and I need to be able to keep track of which shape is being used, and where. If the shapes all have the same name - this is what happens if I use copy/paste - then it's too hard to keep track of which shape refers to which cell.

I'm not looking to add shapes in a range, rather I want to name the shapes that I already have. See the screenshots below. The Group is composed of two pentagons and a rectangle. If I copy this group and paste it, then, each of the following individual objects (pentagons or rectangles) will have the same name, e.g.,. pentagon 1, rectangle 1, etc.

In this example, I have named the pentagon pointing up as "Block1-V, for vertical. I could have kept it as "pentagon 1, and just added the "V", but I'm trying to keep the names short and still be functional. I have a macro that fills the cell color based on the limit, e.g. green, yellow, orange, red, etc.

Since I'm using the data in a cell to fill the value in the vertical shape, I can reference that shape to a particular cell. That's not an issue. But, I want to find a way to name each shape individually. I don't want to add 100 shapes to a document, and have them all be named "Pentagon 1" or "Rectangle 1".

 

[gshock]

Shapes have a TopLeftCell. I don't quite grasp what you're after but have a look at that.
If you're looking at adding shapes in a particular range, can be cell, row and whatever, that's probably the way to go.
If you tell us what you need, not in a bookwork, it should be simple.
Your question could be that you want to add a shape below the last shape in Column A. Give us the height, width and someone will help.

I think this is what I'm looking for.

Shape.Name property (Excel)

Office VBA reference topic

learn.microsoft.com

I will set a range of cells to have a name, and then create a macro that loops through all the shapes on the active sheet and names them accordingly. This will tie in with the shape fill color macro that uses the suffix if the shape name, -V, -H- A (in my case).

 

[jolivanes]

Sub Maybe()
Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.TopLeftCell.Address(0, 0) = "B3" Then MsgBox shp.Name
    Next shp
End Sub

Change the "MsgBox shp.Name" to something like (not tested) shp.Name = "V" & shp.TopLeftCell.Address(0,0)
If you give us a few examples of the shape names with the address for each shape we might be able to get a better solution.

 

[gshock]

Sub Maybe()
Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.TopLeftCell.Address(0, 0) = "B3" Then MsgBox shp.Name
    Next shp
End Sub

Change the "MsgBox shp.Name" to something like (not tested) shp.Name = "V" & shp.TopLeftCell.Address(0,0)
If you give us a few examples of the shape names with the address for each shape we might be able to get a better solution.

Jolivanes, thanks for the suggestion. I was testing the code that you provided, and I was able to get the MsgBox to work with cells, however, these are grouped shapes. So the name is "Group 1", "Group 2", etc. I copied a Group to a new location and ungrouped the shapes and text box. The code still works well.

Instead of calling the name by this method, can I set the names of the shapes based on the text that I have in columns "C" and "F"?