Setting a Chart Title in Excel 2007 using VBA

**Ra21V12eN** · 02-04-2009, 08:25 PM

I had a set of VBA code written against Excel 2003. It worked flawlessly until bringing it into 2007.

I'm sure that the full set of VBA could be written better as I'm just starting my VBA journey, but that is besides the point.

Now after re-creating the VBA code in an *.xlsm Excel 2007 file, this one section isn't working.

As I step through the VBA code, everything works up until this point.

Where it is failing is that the VBA Script is not updating the Chart title to reference the worksheet called "Hidden - Chart Titles" and choosing the text in the incremented row in the 2nd column. There is a default chart title on the sheet as that worksheet is being copied in, and I want it to set the text.

Please Login or Register  to view this content.

Hope that makes sense to someone far smarter than me.

Thank you in advance for any help that you may be able to provide.

~C

**Andy Pope** · 02-05-2009, 05:39 AM

The code works so it must be something to do with the content of variables or the workbook/sheets you have.

Which line raises the error?
what is the value of oRowCnt

post example workbook if you need more help.

**Ra21V12eN** · 02-05-2009, 05:03 PM

Andy,

Thank you for your assistance so far.

I've uncovered one part of the problem. When I re-created the workbook in the 2007 version of Excel, I created the charts on a worksheet as opposed to the way they originally were in the 2003 version. When I realized that I deleted out the charts and recreated them as their own worksheet.

This has solved one part of the issue. The chart title now matches the text in the cell on the Hidden - Chart Titles page at the time that the chart is inserted into the Workbook. It doesn't dynamically update however.

When I look at the chart sheet and click on the title, I would expect the formula that the VBA script created would show up in the Formula Bar as it did in the 2003 version. Once I can get this done, the problem is solved...

I'm not able to share the workbook in its current state. Sorry.

Chris

**Ra21V12eN** · 02-05-2009, 05:31 PM

Ok,

Here is a further update... Getting closer.

With the Macro Recorder On I went and manually edited one of the chart titles and pointed it to the page that contains the chart title.

Excel automatically put an absolute reference in and it looks like this.

='Hidden - Chart Titles'!$B$3

The Macro Recorder doesn't record the fact that I changed the titles...

It used to do that in Excel 2003, why not now?

I attempted to add the $ to the existing formula and it failed. When I went to the chart, it gave me an invalid formula error. I think that if I can get the VBA to include the Absolute Reference to the chart, it will solve the problem...

~c

**Ra21V12eN** · 02-05-2009, 08:27 PM

Finally figured it out...

Here is the resulting code...

Please Login or Register  to view this content.

Now it does exactly what I expect it to do... now to just get the color references and specifics set and I'm all set...

~c

Setting a Chart Title in Excel 2007 using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Setting a Chart Title in Excel 2007 using VBA

Re: Setting a Chart Title in Excel 2007 using VBA

Re: Setting a Chart Title in Excel 2007 using VBA

Re: Setting a Chart Title in Excel 2007 using VBA

Re: Setting a Chart Title in Excel 2007 using VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions