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.

 

[SkipVought]

The name needs to be applied to the chartOBJECT which is a container for the chart rather than the chart itself

THAT depends WHEN you name the chart.

Initially, when the chart is ADDED, it is a CHART SHEET. At that point it is NOT a ChartObject, until it is located to a sheet.

faq707-4811

Skip,

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

 

[GovNewbi]

I will give the variable thing a try. Thanks Skip.

kjv1611 am I blind or did you say you were going to show another thread and then not add the thread? lol

 

[SkipVought]

Unless there's a reason to destroy the chart(s) and start from scratch each time, I'd create the chart and must change the pointers to the source data.

Your four charts could be displayed BY one chart, using controls. You simply control the Title and other text box content and source data in your code.

Your four charts could be displayed IN one chart sheet, using 4 chartobjects, or IN one worksheet, with 4 chartobjects.

I personally like the ONE CHART method, with controls.

Skip,

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

 

[GovNewbi]

I like the one chart idea as well but I have a feeling this could get complicated. I am not very comfortable with the chart objects and such. I have no experience working with these.

 

[xlbo]

Thanks Skip - didn;t know that -->*

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjv1611]

Oops, no you're not blind. I just forgot to paste. Not the first time I've forgotten a link or an attachment or my head..

http://www.ozgrid.com/forum/showthread.php?t=24856

 

[GovNewbi]

Ok so I created a tab to be my chart tab. It is named Chart1 for now. But I can't seem to activate it at the begining of my code. How do I go about doing that so that I can use the activechart. ect for the rest of my code?

 

[SkipVought]

Your chart does not have to be ACTIVATED.

Simply REFERENCE the chart, using the chart name...

ActiveSheet.ChartObjects("YourName").chart.whatever

or

ActiveSheet.ChartObjects("YourName").Activate

Skip,

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

 

[GovNewbi]

But I have two tabs (worksheets) on the bottom of my workbook (CostChart and Sheet1). The table the chart is pulling data from in on Sheet1 as well as the button I have put in to start the macro. I want the macro to bring up the CostChart, make the alterations to it, and show it to the user. This might be what you are telling me but I am just a little confused. I feel I might be in slightly over my head.

 

[SkipVought]

With Sheets("CostChart")
  .Activate
  .ChartObjects("YourName").Activate
end with

Skip,

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

 

[kjv1611]

If Sheet1 is a static data source, might I suggest it'd make things simpler for you if you actually give it an applicable name?

 

[GovNewbi]

How can I write code to change the name of the series to the values from a list on my table.

i.e. this part...

Range("StProj").Activate
Do While ActiveCell.Value <> ""
ActiveChart.SeriesCollection(i).Name = "='Sheet1'!$A$SerNum"
j = j + 1
i = i + 1
SerNum = SerNum + 1
Range("StProj").Offset(j, 0).Activate
Loop

isn't working.

 

[GovNewbi]

For several reasons... I know the range won't select because the costchart is now my active worksheet and StProj is in Sheet1. It also does not like SerNum in the Name portion. But there has to be a better way to do this.

 

[SkipVought]

One of the MANY reasons to avoid the cell selection method for looping!!!!!

  Dim r as range, ws1 as worksheet
  
  Set ws1 = Sheets("Sheet1")

  for each r in ws1.Range(ws1.Range("StProj"), ws1.Range("StProj").end(xldown))
            ActiveChart.SeriesCollection(i).Name = "='Sheet1'!$A$[b]" & SerNum[/b]
            i = i + 1
            SerNum = SerNum + 1
   Next r

Skip,

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

 

[GovNewbi]

THANK YOU! I was playing with that .end(xldown) you told me about earlier but I couldn't see how it all came together. This should work beautifully

 

[GovNewbi]

ok... well it almost works. Here is what I have now...

Sub Graph1()
Dim i As Integer
Dim r As Range
Dim ws1 As Worksheet
Dim SerNum As Integer

i = 1
SerNum = 155
Set ws1 = Sheets("Sheet1")

Application.ScreenUpdating = False

With Sheets("CostChart")
.Activate
End With

ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Col1Graph")
For Each r In ws1.Range(ws1.Range("StProj"), ws1.Range("StProj").End(xlDown))
ActiveChart.SeriesCollection(i).Name = "='Sheet1'!$A$" & SerNum
i = i + 1
SerNum = SerNum + 1
Next r
ActiveChart.ApplyLayout (3)
ActiveChart.ChartTitle.Select
If Range("Divide").Value = True Then
ActiveSheet.ChartObjects("CostChart").Activate
ActiveChart.ChartTitle.Text = "New Title Divide"
Else
ActiveSheet.ChartObjects("CostChart").Activate
ActiveChart.ChartTitle.Text = "New Title"
End If

Application.ScreenUpdating = True

End Sub

The problem is that on the second time through the For/Next I get runtime-error 1004. Invalid Parameter. Also while stepping through I noticed it didn't change the name of the first series.

 

[SkipVought]

"runtime-error 1004. Invalid Parameter." on WHAT statement?

Skip,

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

 

[GovNewbi]

ActiveChart.SeriesCollection(i).Name = "='Sheet1'!$A$" & SerNum


It dosn't like something in this line. Dose this portion...
"='Sheet1'!$A$" & SerNum
point to a value in the cell or do i need a .value or something somewhere?

 

[SkipVought]

What is the VALUE in SerNum when you get the error?

What is the value (string) in Sheet1 Column A for the SerNum value?

Do you have i (whatever the number is) Series in your Chart?

Skip,

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

 

[GovNewbi]

The values seem to be right. The Locals window shows SerNum as 155 then 156 when it pases the increment code. And i is 1 then goes to 2. I don't get the error until the second run through but the first dosn't actualy change the name (so SerNum = 156 when I get the error)

SerNum isn't a value in sheet1 it is just a variable I used to increase the row number.

I have several series in the table. I supose I am assuming it calls them series 1, 2, 3 ect according to the recorded macro.