CODEWITHSUNDEEP
vbaexcel
SXS
SXS

Reputation: 303

How to refer to chart title in Shapes()?

I want to format a chart in a new tab based on some existing data from a worksheet. I can record the pattern using "Save As Template", but I want to create a macro that creates the same format every time. However, I have some problem with referring to the chart. Here's my code.

Sub Macro1()
    Dim GraphTab As Object
    Set GraphTab = ActiveSheet

   'Change everything to Times New Roman
    With ActiveSheet.Shapes(GraphTab).TextFrame2.TextRange.Font
        .NameComplexScript = "Times New Roman"
        .NameFarEast = "Times New Roman"
        .Name = "Times New Roman"
        .Size = 10
    End With
End Sub

I receive an error message starting from this line With ActiveSheet.Shapes(GraphTab).TextFrame2.TextRange.Font.

Ideally, I also want the GraphTab to be updated every time I manually change the tab name. Is that possible?

Upvotes: 1

Views: 1103

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27269

Based on your last comment, this code has been tested to produce the desired results.

Sub Macro1()

    Dim ws As Worksheet
    Set ws = Sheets("NewChartSheet") ' change name as needed

    ws.Range("A1").Formula = "=RIGHT(CELL(""filename"",A1),LEN(CELL(""filename"",A1))-FIND(""]"",CELL(""filename"",A1)))        

    Dim cht As ChartObject
    Set cht = ws.ChartObjects(1) 'since just one chart on page, it's the first one in the collection

    With cht.Chart.ChartTitle
        With .Format.TextFrame2.TextRange.Font
            .NameComplexScript = "Times New Roman"
            .NameFarEast = "Times New Roman"
            .Name = "Times New Roman"
            .Size = 10
        End With
        .Caption = "=NewChartSheet!R1C1"
    End With

End Sub

To understand how the chart title is set up to automatically change if the sheet name is changed see below:

  1. The .Caption = "=NewChartSheet!R1C1" sets the chart title to equal the value in cell A1
  2. The ws.Range("A1").Formula sets the formula to show the sheet name.
  3. The formula in A1 uses the filename argument of the CELL function, which returns the full file path, file name and sheet name, of the referenced cell.
  4. The formula manipulates the format of the filename further to return just the sheet name.

Upvotes: 1

Ralph
Ralph

Reputation: 9444

Maybe this is the solution you've been looking for:

With Sheet1.ChartObjects("Chart 1").Chart.ChartTitle
    .Text = "test"
    .Font.FontStyle = "Times New Roman"
    .Font.Size = 10
End With

If you are using the sheet's code name instead of the sheet name then the code will still work when you are changing the name of the graph sheet. If you meant to change the chart name then I would integrate a loop like so

For Each cht In Sheet1.ChartObjects
    With cht.Chart.ChartTitle
        .Text = "test"
        .Font.FontStyle = "Times New Roman"
        .Font.Size = 10
    End With
Next cht

Upvotes: 0

Related Questions