hatman
Well-known Member
- Joined
- Apr 8, 2005
- Messages
- 2,664
I have the following function buried in an application I created:
It will insert a new shape at specified corrdinates with specified text. This routine gets called in one of two cases: 1) User is loading a text file that contains a bunch of shape info that was exported and saved earlier, or 2) User is inserting a new shape. If 2, then the optional parameter is missing, and Excel automatically generates a new unique shape name that is retained and returned by the function.
EXCEPT
Occasionally, the new shape name is NOT unique... it's a duplicate of one that already exists on the sheet. I have only observed this AFTER the user has loaded a saved file (which specifies each shape name), though it could happen at other times, and I haven't captured it. Does anyone have insight into how Excel generates unique shape names? I wanted to avoid creating a function to generate unique shape names since Excel seemed to do it for me... but if my usage is somehow undermining Excel's ability to maintain unique keys... well, you get the picture.
Code:
Function Insert_Comment_OR_Tool_Shape(msoShape As Integer, _
cLeft As Double, _
cTop As Double, _
cWidth As Double, _
cHeight As Double, _
Comment_Text As String, _
Font_Size As Integer, _
Optional Shpe_Name As String) As String
'msoShape = 5 or 6
' 5 = rounded rectangle (tool/hardware base)
' 6 = octagon (comment)
Application.EnableEvents = False
Blocks.Shapes.AddShape(msoShape, cLeft, cTop, cWidth, cHeight).Select
With Selection
.Characters.Text = Comment_Text
With .Characters(Start:=1, Length:=Len(Comment_Text)).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = Font_Size
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
.HorizontalAlignment = xlCenter
If msoShape = 5 Then
Elect_Tool.CenterOnCell Range(.TopLeftCell, .BottomRightCell)
End If
If Shpe_Name <> "" Then
.Name = Shpe_Name
End If
Insert_Comment_OR_Tool_Shape = .Name
End With
Call Elect_Tool.Select_Visible_Cell
Application.EnableEvents = True
End Function
It will insert a new shape at specified corrdinates with specified text. This routine gets called in one of two cases: 1) User is loading a text file that contains a bunch of shape info that was exported and saved earlier, or 2) User is inserting a new shape. If 2, then the optional parameter is missing, and Excel automatically generates a new unique shape name that is retained and returned by the function.
EXCEPT
Occasionally, the new shape name is NOT unique... it's a duplicate of one that already exists on the sheet. I have only observed this AFTER the user has loaded a saved file (which specifies each shape name), though it could happen at other times, and I haven't captured it. Does anyone have insight into how Excel generates unique shape names? I wanted to avoid creating a function to generate unique shape names since Excel seemed to do it for me... but if my usage is somehow undermining Excel's ability to maintain unique keys... well, you get the picture.