Problems in looping through charts

[musicgold]

Hi,

I am trying to loop through all the charts in a sheet and I am using the following code.

The msgbox shows the chartobjects in the sheet but the For loop fails to detect any chartobjects and doesn’t do anything.

1. Why is this happening?

2. What is the difference between chart.activate and chart.select ?

Dim Cht As ChartObject
 
MsgBox ActiveSheet.ChartObjects.count
 
For Each Cht In ActiveSheet.ChartObjects
…..
 
Next Cht

Thanks.

[frank_m]

Hi musicgold,

I'm not sure if there is much difference, but perhaps activating would be something you might use before changing element's to the chart. - And selecting perhaps for operations such as copying.

Note that activating the chart is usually not necessary.

Below is some sample code that I wrote about 8 months ago at VBForums to assist one of the members there.

I've provided sample code and attached a workbook so you can play around as I think you can learn more that way than to just have me correct your code.
[vba]Option Explicit

Sub MakeChangesToChartWithLoops()
Dim chobj As ChartObject, ShtChrtObjs As ChartObjects, MySheet As Worksheet
Dim vCategories As Variant
Dim iCategory As Variant

On Error GoTo SheetDoesNotExist
Set MySheet = Sheets("Sheet1")
Set ShtChrtObjs = MySheet.ChartObjects
On Error GoTo 0

If ShtChrtObjs.Count > 0 Then
For Each chobj In ShtChrtObjs

With chobj
'.Top =
'.Left =
.Height = 250
.Width = 305
End With

With chobj.Chart
.HasLegend = True
With .ChartTitle.Characters: .Font.Size = 12: .Font.Bold = True: End With
With .Legend: .Font.Size = 10: .Font.Bold = False: .Left = 235: End With

With .Axes(xlValue)
With .TickLabels: .Font.Size = 8: .Font.Bold = False: End With
.HasTitle = True
.AxisTitle.Characters.Caption = "Vertical - Axes - Values"
.AxisTitle.Characters.Font.Size = 10
End With

With .Axes(xlCategory)
With .TickLabels: .Font.Size = 8: .Font.Bold = False: End With
.HasTitle = True
.AxisTitle.Characters.Caption = "Month"
.AxisTitle.Characters.Font.Size = 10
End With

With .SeriesCollection(1).Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2 'sets bar color theme
.ForeColor.TintAndShade = 0
' .ForeColor.Brightness = 0
.Transparency = 0
.Solid
End With

vCategories = .SeriesCollection(1).XValues '.xlCategory
' Loop through all categories
' set color of chart bars to category colors
For iCategory = 1 To UBound(vCategories)

.SeriesCollection(1).Points(iCategory).Interior.ColorIndex = iCategory + 2

.SeriesCollection(1).HasDataLabels = True

With .SeriesCollection(1).DataLabels 'Number labels of bars
'.AutoScaleFont = True
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
End With
End With
Next

' source of the Charts data contained in sheet2
.SetSourceData Sheets("Sheet2").Range("A2:B8"), _
PlotBy:=xlColumns

End With

Next

Else
MsgBox "There are no Charts on the Specified Sheet"
End If

Exit Sub
SheetDoesNotExist:
MsgBox "Specified does not exist"

End Sub[/vba]

[Kenneth Hobs]

I am not sure why you had problem.
[VBA] Dim sh As Object
Dim co As ChartObject

' charts on worksheets
For Each sh In Worksheets
For Each co In sh.ChartObjects
Debug.Print co.Name, "charts on " & sh.Name
Next co
Next sh[/VBA]