-1

While looping through all charts in the book - how can i get name of the sheet on which my chart is located ?

I have folowing code in VBA Excel:

    Sub mytest()
Dim ws As Worksheet
Dim Ct As ChartObject
Dim Wb As Workbook

Set Wb = ActiveWorkbook
Set ws = Wb.ActiveSheet

For Each Ct In ws.ChartObjects
'MsgBox Ct.Chart.Parent.Name
MsgBox Ct.Chart.Name
MsgBox Ct.Chart.Parent.Name

Next Ct

End Sub

when i try this code MsgBox Ct.Chart.Name i get msbbox Name of Sheet & name of Chart in one string: "Sheet3 Chart2"

when i try this code MsgBox Ct.Chart.Parent.Name i get Name of Chart: "Chart2"

but i need to get Name of Sheet on which this chart is located what property of Chart should i use?

VBA_fan
  • 1
  • 1

1 Answers1

0
ActiveChart.Parent.Parent

will give you the worksheet that contains the active chart.

Of course, if you have an ActiveChart, ActiveSheet also gives you the active sheet, which contains the active chart.

Jon Peltier
  • 5,895
  • 1
  • 27
  • 27