Pivotchart's Pie Title

**jartzh** · 02-20-2009, 08:52 AM

I can save a macro that builds the Pie chart from the column. It does everything but let me assign a title to the pie chart. I have to do that manually . Is there a way to assign a title thru a macro?
This is obviously a charting and a macro question but I took a chance and put it under the charting forum.

**Andy Pope** · 02-20-2009, 09:33 AM

something like this

activechart.ChartTitle.Text = "My Title"

**jartzh** · 02-20-2009, 09:38 AM

Brilliant!
Is there a way to put the values of cell A1 in the title?

**Andy Pope** · 02-20-2009, 09:40 AM

activechart.ChartTitle.Text = "A1 value is " & worksheets("Sheet1").range("A1").value

Or you can link the chart title to the cell.
http://www.andypope.info/tips/tip001.htm

**jartzh** · 02-20-2009, 10:16 AM

Andy,
Can I use activesheet to specify the cell?

**Andy Pope** · 02-20-2009, 10:17 AM

Only if the activechart is an object within a worksheet.
Otherwise the activesheet will be the chart sheet and chart sheets do not have cells.

**jartzh** · 02-20-2009, 10:35 AM

Andy,
I have the data for the chart in col A and I would like cell B1 to be the title. But I want it to work on any of the 12 worksheets in the workbook.
So I need to say "just grab the value in B1 on the active worksheet".

Is that possible?

**Andy Pope** · 02-20-2009, 10:49 AM

As I said if the Activechart is on the same sheet as the cell B1 then yes.

activechart.ChartTitle.Text = "A1 value is " & activesheet.range("B1").value

If your chart is on it's own chart sheet then no as the activesheet will then be the chart and not any of those with a B1 cell

**jartzh** · 02-20-2009, 11:02 AM

Andy,
Thats why I am having this problem. I have 12 worksheets and want to build a single macro that will build a chart on whatever worksheet I am on at that time. That part works fine but I can't title it.

Just fishing here but :
What about if I had a seperate worksheet that would get the title from it based on the name of the active worksheet?

**Andy Pope** · 02-20-2009, 11:04 AM

can you post what you have so far?

**jartzh** · 02-20-2009, 11:57 AM

Andy,
Sure here it is. It only works up thru the job experience worksheet now.
If you go to the classificationdata worksheet and use the removechart macro to clean off the worksheet, then use the buildchart macro to build the chart from the contents of col A.

I think since it uses a template so you wont be able to build the chart .