Excel 2007: create specifically named copies of an autoshape?

[ker_01]

I have an autoshape, currently named "Oval 2"

I'm almost finished with the analytical part of my VBA, and now just need to
render it to the UI. The user (an internal customer) will type in a list of
numbers on Sheet1, and I need to create a copy of the autoshape for each
number entered. That part is easy;

Sheet2.Shapes("Oval 2").Copy
Sheet2.Paste

However, I immediately lose track of which new autoshape is which, since I
haven't figured out how to assign specific names to the new autoshapes (e.g.
"MyOval99").

I need to keep a master list of all the autoshapes on the page so I can
delete them when the user puts in a new list of values, so I can create a new
set of autoshapes to match the new values. The total number of values the
user might enter is unknown.

Question: is there a way to name the autoshape from VBA while creating it?
I've played with the paste syntax, but haven't found anything that works yet.

From there, I think it will be easy to delete all the shapes when they are
no longer needed.

Thanks!
Keith

 

[papou]

Hi Keith
If your **only alternative** is actually copying and pasting, you could
simply use:
Sheet2.Shapes("Oval 2").Copy
Sheet2.Paste
Selection.Name = "Oval " & i
Where "i" can be an integer variable you define earlier in your code

But I would suggect you try and find some better way to achieve this...

HTH
Cordially
Pascal

 

[ker_01]

I can create and format the autoshapes via code; the issue I was running into
was being able to move them or delete individual shapes after I had already
added later shapes (e.g. it was no longer selected).

I welcome suggestions on "better ways" to achieve this- I'm always eager to
improve my approach!

Best,
Keith

 

[ker_01]

Strangly, when I initiate this code from the VBE, it works fine, even at full
speed (F5). However, when I call the macro from the developer toolbar, it
fails to assign the new name to the shape

I did insert a DoEvents right after the Selection.Name = "x" assignment to
make sure it had the opportunity to process the command, but still no dice.

Is there anything else that I should be doing? I would have expected code
that works directly in the VBE to work the same when called from the Excel
instance.

Thank you,
Keith

 

[ker_01]

Adding Sheet2.Activate was sufficient to get it working.
Thank you!

I still welcome alternative approaches, if there is a better way to create,
size, and place autoshapes in a way that they are easy to continue to address
even after another shape has been selected.

Best,
Keith