Naming and referring to charts

[travis]

I have a spreadsheet with a bunch of charts on it that I want to
manipulate in code.

1) What is the easiest way to find out what a chart is called? I've
had to resort to recording a macro, clicking on a chart, then reading
the code to find out what Excel has called it.

2) How do I give a chart a meaningful name, like "chart_income" instead
of Excel's default number?

3) One of the things I want to do includes duplicating a worksheet with
a complex scenario on it. The scenario has a whole heap of VBA
controls, charts and all kinds of stuff. When I duplicate the
worksheet I'd like it if the controls, which include various buttons
for modifying charts, I'd like it if my buttons, including the ones
which modify the charts, to still work. They don't work because the
charts rename themselves when I copy the worksheet.

I know with issue 3, one way around it would be to have the scenario
spreadsheet duplicated and all buttons recoded and "duplicate" the
projection by unhiding what is already there, and using a routine that
plugs the data from scenario 1 into scenario 2, but my main problems
with that are...
* if I make a change to scenario 1 I don't want to have to make the
same change to the other scenarios, especially since I modify scenarios
constantly.
* each scenario is huge, and a spreadsheet with just one scenario is
about 4 megabytes unzipped (though only 600K zipped). If I had
multiple scenarios, the spreadsheet would become enormous, which is a
particular problem since I have to email it to a lot of people
frequently.
* I may want more than just a couple of scenarios, maybe even dozens of
them, so I'd prefer to just be able to create duplicates on the fly.

Travis

 

[Guest]

I can answer the first 2 parts at least, travis.

1. if it is inside a sheet, just press shift and click on the chart.
Look in the name box for the name.
If it is on its own tab, then the name of the chart is simply the tab name.

2. press shift and click on the chart. it is now selected with a white
border not a black one. Change the name in the name box and press return
(don't just click away).

3 . when I tried this, I named my chart "frog". Then I copied the sheet
using move or copy from the menu on the tab. The chart in the new sheet was
also called frog. So I think that this question might solve itself

 

[travis]

I can answer the first 2 parts at least, travis.

1. if it is inside a sheet, just press shift and click on the chart.
Look in the name box for the name.
If it is on its own tab, then the name of the chart is simply the tab name.

2. press shift and click on the chart. it is now selected with a white
border not a black one. Change the name in the name box and press return
(don't just click away).

3 . when I tried this, I named my chart "frog". Then I copied the sheet
using move or copy from the menu on the tab. The chart in the new sheet was
also called frog. So I think that this question might solve itself

Thanks a lot Allllen, it worked perfectly. To make the code work on
all copies, I had to change a few other things in my code, like
replacing "Worksheetname" with ActiveSheet.Name in a few places.

Travis