+ Reply to Thread
Results 1 to 5 of 5

Setting a Chart Title in Excel 2007 using VBA

  1. #1
    Registered User
    Join Date
    12-09-2008
    Location
    Tucson, AZ
    MS-Off Ver
    Office 2013
    Posts
    37

    Setting a Chart Title in Excel 2007 using VBA

    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
    Last edited by Ra21V12eN; 02-05-2009 at 08:27 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,244

    Re: Setting a Chart Title in Excel 2007 using VBA

    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.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    12-09-2008
    Location
    Tucson, AZ
    MS-Off Ver
    Office 2013
    Posts
    37

    Re: Setting a Chart Title in Excel 2007 using VBA

    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

  4. #4
    Registered User
    Join Date
    12-09-2008
    Location
    Tucson, AZ
    MS-Off Ver
    Office 2013
    Posts
    37

    Re: Setting a Chart Title in Excel 2007 using VBA

    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

  5. #5
    Registered User
    Join Date
    12-09-2008
    Location
    Tucson, AZ
    MS-Off Ver
    Office 2013
    Posts
    37

    Re: Setting a Chart Title in Excel 2007 using VBA

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1