I have created a custom shape on my sheet to use as a template. In the code I
use the following to copy the shape and rename it:

'Copy bar
oColorBar.Copy

'Paste to Cell
Me.Paste Destination:=rColorCell

'Rename
Me.Shapes(Me.Shapes.Count).name = cTPM & "_" & rCell.Row

Now this works fine in Excel 2003 or before. I tested this in Excel 2007 and
this fails, as it seems the shapes collection is now sorted and the new shape
is not necessarilty added to the bottom of the collection. So I still want to
copy my tempalte shape to the sheet and get a handle on it, but I don't know
it's index in the shapes collection, and it's name is some default name with
a number after it.

I essentially want to do a set myShape = Shapes.add(customShape). Any ideas?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

This works in pre xl2007 versions...
'--
Sub PutErThere()
Dim shp As Shape
Set shp = Me.Shapes("oColorBar").Duplicate
With Me.Range("oColorCell")
shp.Top = .Top
shp.Left = .Left
End With
shp.Name = "Mush"
Set shp = Nothing
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"J Streger"
wrote in message
I have created a custom shape on my sheet to use as a template. In the code I
use the following to copy the shape and rename it:

'Copy bar
oColorBar.Copy

'Paste to Cell
Me.Paste Destination:=rColorCell

'Rename
Me.Shapes(Me.Shapes.Count).name = cTPM & "_" & rCell.Row

Now this works fine in Excel 2003 or before. I tested this in Excel 2007 and
this fails, as it seems the shapes collection is now sorted and the new shape
is not necessarilty added to the bottom of the collection. So I still want to
copy my tempalte shape to the sheet and get a handle on it, but I don't know
it's index in the shapes collection, and it's name is some default name with
a number after it.

I essentially want to do a set myShape = Shapes.add(customShape). Any ideas?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

thank you greatly :) Never even knew that function existed. Much nicer than
copy/paste.
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003



"Jim Cone" wrote:

This works in pre xl2007 versions...
'--
Sub PutErThere()
Dim shp As Shape
Set shp = Me.Shapes("oColorBar").Duplicate
With Me.Range("oColorCell")
shp.Top = .Top
shp.Left = .Left
End With
shp.Name = "Mush"
Set shp = Nothing
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"J Streger"
wrote in message
I have created a custom shape on my sheet to use as a template. In the code I
use the following to copy the shape and rename it:

'Copy bar
oColorBar.Copy

'Paste to Cell
Me.Paste Destination:=rColorCell

'Rename
Me.Shapes(Me.Shapes.Count).name = cTPM & "_" & rCell.Row

Now this works fine in Excel 2003 or before. I tested this in Excel 2007 and
this fails, as it seems the shapes collection is now sorted and the new shape
is not necessarilty added to the bottom of the collection. So I still want to
copy my tempalte shape to the sheet and get a handle on it, but I don't know
it's index in the shapes collection, and it's name is some default name with
a number after it.

I essentially want to do a set myShape = Shapes.add(customShape). Any ideas?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003