How to reference embedded chart?

Question

I am trying to assign the text in cells A1 and B1 to chart titles for

CHART1
CHART2

and

CHART3
CHART4

I am trying to loop through the workbook for the specific embedded chart which is located in random sheets.

I am getting

"Member or data member not found"

on With ThisWorkbook.Worksheets.Shapes(chtname).Chart.

Sub ChartTitle()

    Dim chartNames As Variant
    
    For Each chtname In Array("CHART1", "CHART2")
        With ThisWorkbook.Worksheets.Shapes(chtname).Chart
            .HasTitle = True
            .ChartTitle.Text = ThisWorkbook.Worksheets.Range("A1")
        End With
    Next

    For Each chtname In Array("CHART3", "CHART4")
        With ThisWorkbook.Worksheets.Shapes(chtname).Chart
            .HasTitle = True
            .ChartTitle.Text = ThisWorkbook.Worksheets.Range("B1")
        End With
    Next

End Sub

ThisWorkbook.Worksheets.Range("B1") does not have any meaning... Would you like to use a specific worksheet, or the sheet found during iteration? — FaneDuru
– FaneDuru, Commented Aug 20, 2020 at 12:18

FaneDuru · Accepted Answer · 2020-08-20 12:20:47Z

1

Since you do not answer my questions, please try the next code. Based on "located in random sheets", the code assumes that the charts in discussion are embedded ones. Since the code ThisWorkbook.Worksheets.Range("B1") does not have any meaning in VBA, the code allocate the values from the sheet where it is located:

Sub ModifyChartTitle()
    Dim sh As Worksheet, chObj As ChartObject

    For Each sh In ThisWorkbook.Sheets
        For Each chObj In sh.ChartObjects
            If chObj.Chart.HasTitle Then
                Select Case chObj.Name
                     Case "CHART1", "CHART2"
                        chObj.Chart.ChartTitle.Text = sh.Range("A1").Value
                     Case "CHART3", "CHART4"
                        chObj.Chart.ChartTitle.Text = sh.Range("B1").Value
                End Select
            End If
        Next
    Next
End Sub

If not embedded charts, or the chart Title must be collected from a specific worksheet, please better specify what is to be done...

answered Aug 20, 2020 at 12:20

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kaizan Over a year ago

Hi FaneDuru, Sorry was without net. Exactly embedded charts! Flawless modification. Thanks :)

FaneDuru Over a year ago

@Kaizan: Glad I could help! I was preparing a second piece of code dealing with the chart sheets...

Collectives™ on Stack Overflow

How to reference embedded chart?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related