Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: create specifically named copies of an autoshape?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: create specifically named copies of an autoshape?
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 "papou" wrote: 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" a écrit dans le message de news: ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: create specifically named copies of an autoshape?
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 "papou" wrote: 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" a écrit dans le message de news: ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: create specifically named copies of an autoshape?
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 "papou" wrote: 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" a écrit dans le message de news: ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Back-up copies | Excel Discussion (Misc queries) | |||
autoshape on my chart got locked - Excel 2007 | Charts and Charting in Excel | |||
Trouble positioning an autoshape object using .Top in Excel 2007 | Excel Programming | |||
why does excel create multiple copies (as many as 18) of a file? | Excel Discussion (Misc queries) | |||
Why does Excel create multiple copies of the same file? | Excel Discussion (Misc queries) |