CODEWITHSUNDEEP
excelvba
Kaizan
Kaizan

Reputation: 43

How to reference embedded chart?

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

Upvotes: 0

Views: 144

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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...

Upvotes: 1

Related Questions