Graph Names in VB 1

[GovNewbi]

I have recorded a macro to generate a graph from a table that is filled with information from a userform. When I record the macro it gives the graph a name like "Graph 1" but then when I clear the info in the table and run the macro again it does not recognize the name "Graph 1" anymore. It also does not want to let me rename it using activechart.name = "name" HELP.

 

[GovNewbi]

ok. What I am doing is ploting one of four graphs depending on what the data in the table looks like. Here are four examples of what the tables will look like for the four different graphs...

Table Co-Generation Plants
002 100
003 200
005 300
006 400
*In this one I know I am plotting format one because there is only one cost type and multiple project numbers (002,003 ect). Also earlier in the code it was defined as Capital not Operating therefore it is a column graph with the project numbers along the x-axis. There can be any number of projects and the name for the cost will change.

Table 004
Co-Generation Plants 100
Delineation & Development 200
Extraction / Tailings 300
Mining Equipment 400
Upgrading Facilities 500
Utility Plants 600
* This one would also be capital, however it is format 2 because there is only one project (004) and multiple costs. There can be any number of costs ranging in name and the project number can also change (but there will always only be one project). This should make a stacked column graph.


I won't get into the other 2 right now. I have a feeling if I can get these two working I will be able to get the other two running as well. They are a little more complex.

 

[SkipVought]

There can be any number of projects and the name for the cost will change.

I assume that the name for the cost will be substituted, either by VALUE or by reference in a fixed cell, the reference of which will never change, like Sheet1!B1. It will NEVER be B2 or C1.

The RANGE for the projects will change, and that's fine. Best to define those ranges as Named Ranges and then reference each Name in the Chart Data Series.


Second chart: "This should make a stacked column graph."
Huh? How do you stack that?
Same issues regarding the name and ranges.
Stacked might work, if you had multiple projects like...
[tt]
Table 004 005........
Co-Generation Plants 100 200
Delineation & Development 200 100
Extraction / Tailings 300 400
Mining Equipment 400 600
Upgrading Facilities 500 500
Utility Plants 600 300
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

It will be a stack of costs. One column that is the project and then each cost being a different colour so that the stack makes the total of all the selected costs.

The name of the costs will be inserted using a set location. This isn't causing any problems. What is causing the problem is the variation in the number of projects/costs. It changes the number of series. If I make a graph with only 3 series and then I try to run the macro again and make a graph with 9 series (i.e. project numbers) the program only names the first three then gives me the error i mentioned earlier on this line...
ActiveChart.SeriesCollection(i).Name = "='Sheet1'!$A$" & SerNum
because it does not recognise i past 3 and yet it has not reached the end of the list. i.e. this hasn't been completed...
For Each r In ws1.Range(ws1.Range("StProj"), ws1.Range("StProj").End(xlDown))

Then say I try the second graph type, I still have 3 series along the x-axis and the graph goes all strange because it does not know to reduce it to one column for the stacked graph.

I have tried changing these things and recording it but the macro recorder does not record "switch row and column" and The biggest problem is that I can't figure out how to set the number of series to equal the number of input values that I will be naming using the code above.

 

[SkipVought]

It changes the number of series.

It does NOT. It should NOT.

ONE SERIES. MULTIPLE (variable) data points.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

Maybe I am not using the word series correctly but it does have a bunch and it won't work. When I say series I am talking about the list under "select data source" and "legend entries (series)". I need there to be multiple for example in the second graph type so that it will show all the different cost types.

 

[SkipVought]

OK I see what you mean on the stacked column chart. I'd write a routine to delete all but one series, then use SeriesCollection.Add for each additional series.

But on the FIRST chart, you have ONE series.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

YES EXACTLY! lol I'm relly glad we are on the same page now. ok so here is my origional question... how can I write a code that will
a) delet the series present
b) create the same number of series as I have data to put in (keeping in mind that there may be 1,5, or 10 or whatever number of costs to be put in as series)

 

[SkipVought]

The other thing that you coud try doing is

1. switch the row/column orientation in the Chart Data.

2. redefine the Chart Data Range in the Chart Data.

3. switch the row/column orientation back.

That way, you do not have to mess with adding individual series.

Play with that manually and then code it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

I did play with that for a while and it was working for some but I can't figure out how to code "switch row/column

 

[SkipVought]

Did you try turning on your macro recorder?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

lol ya i did do that.

Turns out you can't record it from inside the select data tab but if you just do it under data as it's own tab it records. duno why.

 

[SkipVought]

Then you discovered the chart's SetSourceData Method. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

So here's a tip regarding 'adjusting' the range to the chart, assuming that the source data starts in Sheet1!A1...

   with Sheets("Sheet1")
      .ChartObjects(1).Chart.SetSourceData _
         Source:=.Cells(1, 1).CurrentRegion, _
         PlotBy:=xlColumns
   end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

actually the xlRows property of PlotBy is the one to use to readjust the stack.

    With Sheet1
        .ChartObjects(1).Chart.SetSourceData _
            Source:=.Cells(1, 1).CurrentRegion, _
            PlotBy:=xlRows
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}