Results 1 to 3 of 3

Thread: Chart Title

  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Quincy, MA
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Chart Title

    I have a monthly spreadsheet in which I create the same bar chart across 15 different clients. The title reflects both the month and year. The question is, is there something similar to "Find and Replace" that will allow me to change November 2013 to December 2013 without having to go into each and every chart? I don't really know VBA but am willing to learn how to use it. Using Windows 2010. Thank you

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If the date is in a cell and you select that date when initially creating the bar chart, the date will be reflected in the chart title. Then, when you change the date in that cell, the chart will automatically change as well. Will that work for you?

    Alternatively, if you've just graphed the data and then use Chart Tools | Layout | Labels | Chart Title you can click in the formula bar and type: =Sheet1!A1 if your title is in A1 on Sheet1. Then, when you change the entry in A1, the title on the chart will change.

    But, maybe I misread and you want the title on separate sheets. Steve's VBA will do that, obviously. But, you could also make the chart title reference =Sheet1!A1 for all of the chart titles on all of the separate sheets.
    Last edited by kweaver; 2014-01-07 at 14:37.

  3. The Following User Says Thank You to kweaver For This Useful Post:

    Thanos (2014-01-07)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This will loop through the chart sheets and the chart objects within all the worksheets
    Code:
    Option Explicit
    Sub FindReplaceChartTitles()
      Dim wks As Worksheet
      Dim Cht As Chart
      Dim chtObj As ChartObject
      Dim strFind As String
      Dim strReplace As String
    'Change as desired
      strFind = "Old Title"
      strReplace = "New Title"
    
      'Loop through charts
      For Each Cht In ActiveWorkbook.Charts
        Cht.ChartTitle.Text = _
          Application.WorksheetFunction.Substitute( _
            Cht.ChartTitle.Text, strFind, strReplace)
      Next
      'loop through worksheets
      For Each wks In ActiveWorkbook.Worksheets
        'Loop through chart objects
        For Each chtObj In wks.ChartObjects
        chtObj.Chart.ChartTitle.Text = _
          Application.WorksheetFunction.Substitute( _
            chtObj.Chart.ChartTitle.Text, strFind, strReplace)
        Next
      Next
    End Sub
    I think a better option would be to link the titles to a cell, and then in the future, you could change the cell and the names would all update.
    Code:
    Option Explicit
    Sub ChangeChartTitlesToFormula()
      Dim wks As Worksheet
      Dim Cht As Chart
      Dim chtObj As ChartObject
      Dim strReplace As String
    'Change cell reference as desired
      strReplace = "=Sheet1!$A$1"
    
      'Loop through charts
      For Each Cht In ActiveWorkbook.Charts
        Cht.ChartTitle.Caption = strReplace
      Next
      'loop through worksheets
      For Each wks In ActiveWorkbook.Worksheets
        'Loop through chart objects
        For Each chtObj In wks.ChartObjects
          chtObj.Chart.ChartTitle.Caption = strReplace
        Next
      Next
    End Sub
    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    Thanos (2014-01-07)

Posting Permissions

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