Naming AutoShapes - Bad Idea??

[Cyberdude]

I use a LOT of AutoShape block arrows to move my sheets from chart to chart and back again. I am contemplating assigning names to the arrows rather than trying to keep the AutoShape numbers correct in my code. But I'm not sure if it's a good or bad idea.

First, I noticed that after I name an AutoShape, the name doesn't appear in the list of names. Does it appear anywhere else?

Then I noticed that if I copy a named AutoShape, the name shows up on the copy (Phfffft!), so then I have two AutoShapes with the same name. I can correct that by renaming the copy. However, there doesn't seem to be a way to delete an AutoShape's name. Once it's there, it's there forever. Is that true or false??

I wondered what happens to the name when I delete the AutoShape that it's attached to? (Do I care??)

[Bob Phillips]

First, I noticed that after I name an AutoShape, the name doesn't appear in the list of names. Does it appear anywhere else?

Do you mean the list in the Names box to the left of the formula b ar, and Insert>Name>define..? If so, it wouldn't as they are referring to workbook/worksheet names, and shapes are not part of a worksheet, but exist on a layer separate from the sheet.


Then I noticed that if I copy a named AutoShape, the name shows up on the copy (Phfffft!), so then I have two AutoShapes with the same name. I can correct that by renaming the copy. However, there doesn't seem to be a way to delete an AutoShape's name. Once it's there, it's there forever. Is that true or false??

Welcome to the world of objects and container objects.

As I am assuming that you want to n ame them so that you can address them more easily in code, the best thing to manage this is to add the shape through code. That way, you can assign a name as you create them, using the current handle. Like so



Dim shp As Shape
With ActiveSheet.Shapes
Set shp = .AddShape(msoShapeRightArrow, 225, 160, 340, 75#)
shp.Name = "Bill"
Set shp = .AddShape(msoShapeRightArrow, 225, 200, 340, 78#)
shp.Name = "Ben"
End With



I wondered what happens to the name when I delete the AutoShape that it's attached to? (Do I care??)

It is just a property of an item in the shapes collection. When the shape gets deleted, its entry in the collection goes, so that name is no longer in existence. So the answer is, no you don't care!
.

[Cyberdude]

Thanx, xld, I think you answered my Q's. I rarely add AutoShapes via VBA. Most of the time I do it manually, which makes sense in my environment. However, I do use VBA code to change their color a lot, so I have to include their AutoShape numbers in the code (usually in a table).
I just thought naming them might be a benefit.
Yes, the list I was referring to is the one showing the "defined" names. Too bad. Woulda been nice to have the names show up there. I haven't tested it yet, but one wonders what VBA does if the code refers to a name that is assigned to two or more AutoShapes as a consequence of copying.
Anyway, thanks for the answers and insight.

[Bob Phillips]

Yes, the list I was referring to is the one showing the "defined" names. Too bad. Woulda been nice to have the names show up there. I haven't tested it yet, but one wonders what VBA does if the code refers to a name that is assigned to two or more AutoShapes as a consequence of copying.

Zorder maybe?

[Innany]

I am new to VBA and just saw your posting, and I am trying to do something similar. I have about 100 arrows (autoshapes) on the sheet, each in a separate cell vertically, which I need to rotate and change colors based on specific condition. I have the function which determines the color and direction, I can't figure out though how to go from shape to shape and change it. Any help is appreciated.

Thank you

[shades]

Howdy. I use an Admin worksheet (called "Work" in this example) that has the name of each AutoShape (MattAS1, MattAS2, etc.). Then the function associated with it to determine what happens to the direction of the arrow and the color change. In code, then I use i to loop through each one. This was a specific project with 68 AutoShapes.



Sub OriginalValueCheck()
'This checks the cells in Column A on worksheet Work (rows 10 through 77)
Application.ScreenUpdating = False
Dim i As Long
Sheets("Work").Select
For i = 2 To 73
Select Case Cells(i, 1).Value
Case Is < 0: RedAutoShape i
Case Is > 0: GreenAutoShape i
Case 0: OrangeAutoShape i
End Select
Next i
OriginalValueCheckS
Sheets("Highlights").Select
Application.ScreenUpdating = True
End Sub
Sub RedAutoShape(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattAS " & i)
.AutoShapeType = msoShapeDownArrow
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 10
.Line.ForeColor.SchemeColor = 10
.Rotation = 0#
End With
Sheets("Work").Select
End Sub

Sub GreenAutoShape(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattAS " & i)
.AutoShapeType = msoShapeUpArrow
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 57
.Line.ForeColor.SchemeColor = 57
.Rotation = 0#
End With
Sheets("Work").Select
End Sub

Sub OrangeAutoShape(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattAS " & i)
.AutoShapeType = msoShapeLeftRightArrow
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 52
.Line.ForeColor.SchemeColor = 52
.Rotation = 0#
End With
Sheets("Work").Select
End Sub

Sub ObjectCount()
MsgBox ActiveWorkbook.Worksheets("Highlights").Shapes.Count
End Sub

Sub OriginalValueCheckS()
'This checks the cells in Column A on worksheet Work (rows 2 through 9)
'Special AutoShapes - different direction/color from first ValueCheck macro

Dim i As Long
Sheets("Work").Select
For i = 2 To 9
Select Case Cells(i, 7).Value
Case Is < 0: GreenAutoShapeS i
Case Is > 0: RedAutoShapeS i
Case 0: OrangeAutoShapeS i
End Select
Next i
Sheets("Highlights").Select
End Sub

Sub RedAutoShapeS(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattSAS " & i)
.AutoShapeType = msoShapeDownArrow
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 10
.Line.ForeColor.SchemeColor = 10
.Rotation = 180#
End With
Sheets("Work").Select
End Sub

Sub GreenAutoShapeS(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattSAS " & i)
.AutoShapeType = msoShapeUpArrow
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 57
.Line.ForeColor.SchemeColor = 57
.Rotation = 180#
End With
Sheets("Work").Select
End Sub

Sub OrangeAutoShapeS(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattSAS " & i)
.AutoShapeType = msoShapeLeftRightArrow
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 52
.Line.ForeColor.SchemeColor = 52
.Rotation = 0#
End With
Sheets("Work").Select
End Sub

[Innany]

Thank you so much, looks like exactly what I need, will try it tomorrow.