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]

Hi,

what application?

Graph 1 sounds like Power Point.

Skip,

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

 

[GovNewbi]

Excel 2007

 

[SkipVought]

Strange!

When I insert a CHART in my Excel 2007, it defaults to Chart1. Excel specifically refers to CHART objects and not GRAPH objects.

There is an old, old, clunky MS GRAPH object. I hope you're not using it. You have to go thru some hoops to get it.

Skip,

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

 

[GovNewbi]

ya sry it is called chart1... Brain and hands wern't cooperating when I typed that out

 

[SkipVought]

Please post your code.

Skip,

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

 

[xlbo]

When you create a chart, Excel will give it a name (Chart1). If, without closing the workbook, you create another chart, this time it will be called chart2 etc etc - that is why referencing "chart1" will not work

If you post your code so we can see how the chart is created we can show you how to rename it to a generic name that you will be able to reference

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

 

[GovNewbi]

Here is an example of of one the graph codes I have. It was recorded then I made some alterations. Anyway i have the name problem on this one and also a problem with selecting the range. i tried using a variable called SerNum but it dosn't seem to like that. Any input on this code would be helpful. Thanks.

Sub Graph1()
Dim j As Integer
Dim i As Integer
Dim SerNum As Integer

j = 0
i = 1
SerNum = 155

Application.ScreenUpdating = False

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("B154:B157")
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
ActiveChart.ApplyLayout (3)
ActiveChart.ChartTitle.Select
If Range("Divide").Value = True Then
ActiveSheet.ChartObjects("Chart 3").Activate
'ActiveChart.ChartTitle.Text = "New Title"
Else
ActiveSheet.ChartObjects("Chart 3").Activate
'ActiveChart.ChartTitle.Text = "New Title"
End If
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsNewSheet

Application.ScreenUpdating = True

End Sub

 

[GovNewbi]

Oh ya.. i have this line apostophed out because I havent decided what the titles will be yet

'ActiveChart.ChartTitle.Text = "New Title"

 

[SkipVought]

Please post questions/code that is relevant!

There is NOTHING in the code you posted that has ANYTHING to do with naming a chart!!!

Skip,

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

 

[GovNewbi]

In the if else part there is reference to "chart 3". this was created when I recorded the macro and now when I run it it has no idea what "chart 3" is. Like I said I tried naming using the activechart.name = "name" but it didn't help so I took that out.

 

[kjv1611]

Here's the full code, slightly reformatted (no actual code changes) for reference:

Sub Graph1()
	Dim j As Integer
	Dim i As Integer
	Dim SerNum As Integer

	j = 0
	i = 1
	SerNum = 155

	Application.ScreenUpdating = False

	ActiveSheet.Shapes.AddChart.Select
	ActiveChart.ChartType = xlColumnClustered
	ActiveChart.SetSourceData Source:=Range("B154:B157")
	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
	
	ActiveChart.ApplyLayout (3)
	ActiveChart.ChartTitle.Select
	
	If Range("Divide").Value = True Then
		ActiveSheet.ChartObjects("Chart 3").Activate
		'ActiveChart.ChartTitle.Text = "New Title"
	Else
		ActiveSheet.ChartObjects("Chart 3").Activate
		'ActiveChart.ChartTitle.Text = "New Title"
	End If
	
	ActiveSheet.ChartObjects("Chart 3").Activate
	ActiveChart.ChartArea.Select
	ActiveChart.Location Where:=xlLocationAsNewSheet
		
	Application.ScreenUpdating = True

End Sub

 

[kjv1611]

This is the only code related to remaining anything, and it isn't the chart:

	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

 

[GovNewbi]

This part...

If Range("Divide").Value = True Then
ActiveSheet.ChartObjects("Chart 3").Activate
'ActiveChart.ChartTitle.Text = "New Title"
Else
ActiveSheet.ChartObjects("Chart 3").Activate
'ActiveChart.ChartTitle.Text = "New Title"
End If

ActiveSheet.ChartObjects("Chart 3").Activate

has refreences to "Chart 3". I want to know how to name the chart when it is created (in the begining of the code) so that when I get to this part my computer isn't asking wtf "chart 3" is. lol.

 

[kjv1611]

In the if else part there is reference to "chart 3". [HIGHLIGHT]this was created when I recorded the macro and now when I run it it has no idea what "chart 3" is.[/HIGHLIGHT] Like I said I tried naming using the activechart.name = "name" but it didn't help so I took that out.

If you're talking about the same macro, it's going to create a new chart each time you run it. Therefore, you cannot use static names to refer to said chart.

If you're wanting to refer to previous charts... say you run the macro today, create Chart1, and tomorrow you run again, it'll create Chart2.. and you want to reference Chart1, then you need a way to reference said chart.

The best way of doing this would be to give meaningful names to begin with. If nothing, you could give the date associated with the day when the chart was made.

Or if there is no meaninful way of naming the chart by department, creator, date, or whatever, then perhaps you should come with a naming scheme of your own. Then, have a worksheet where you store the names of each chart, date created, who created, whatever info you might need/want, and then if the code needs to reference a particular previous chart, you can find the name of that chart (via code) in the worksheet range which stores the list of chart names.

I may be way off, but it sounds to me like this is a workbook used on a regular basis, not just creating a new workbook, and being done with it.

 

[kjv1611]

Okay, so you are wanting to know how to name the chart when it's created. That would make things simpler, wouldn't it.

 

[xlbo]

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



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

 

[SkipVought]

faq707-4811

  Dim oChart as Chart, oChartObj as ChartObject

  Set oChart = Charts.Add
  With oChart
    .name = "Whatever"
  End with

Skip,

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

 

[GovNewbi]

Ya this will be a workbook used often. There are four kinds of charts that will be generated from it. A user will input info into a userform, this will be put into a table, then one of the four charts will be generated depending on the info in the table. Everytime the macro is run the table is erased.

So I am wondering if this would work then. I could create the four charts. Have them as their own tabs. Then as the info in the table changes the charts should change and the macro can simply call the appropriate chart. Do you think that would be the best way to do it?

 

[kjv1611]

Here's another thread in another popular Excel forum that gives a couple of different methods as well. Personally, I like Skip's method, above. Maybe I'm just too variable.