VBA charts and Series

[MRxlicius]

I have the following code, to loop through all the charts in a worksheet and all the series in each chart and change for example the number format and the separator. However i am getting an error: on the line "For Each mychart In mysheet.ChartObjects". Could you please help me.

Sub LoopThroughCharts()

Dim mysheet As Worksheet
Dim CurrentSheet As Worksheet
Dim mychart As ChartObject
Dim myseries As Series


Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet

  For Each mychart In mysheet.ChartObjects
    mychart.Activate

  For Each myseries  In myChartObject.Chart.SeriesCollection
    Selection.NumberFormat = "#'##0"
    Selection.Separator = "; "
    Next
  
  
  Next

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

 

[Andy Pope]

You have dim'd some objects but then not used them, instead using the selection object which at the time in the code is the wrong object
I have assumed the Selection was in reference to the data labels.

As the code is now using the objects there is no need to select or activate anything

Sub LoopThroughCharts()

Dim mysheet As Worksheet
Dim CurrentSheet As Worksheet
Dim mychart As ChartObject
Dim myseries As Series


Application.ScreenUpdating = False
Application.EnableEvents = False

Set mysheet = ActiveSheet
 
  For Each mychart In mysheet.ChartObjects

  For Each myseries In mychart.Chart.SeriesCollection
    myseries.DataLabels.NumberFormat = "#'##0"
    myseries.DataLabels.Separator = "; "
    Next
 
  Next

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub