CODEWITHSUNDEEP
vbaexcelcharts
David
David

Reputation: 177

VBA Determine order of objects in "for each loops"

How can I take control of the order my charts appaer in the for each loop? Right now the charts are selected by random; Chart 2, Chart 7, Chart 5 etc.

Should I rename the pre-defined chart names? If so, how do I do that?

    ...
    'Loop charts
    ThisWorkbook.Activate
    Dim objCht As Object
    Dim i As Integer
    i = 1
    For Each objCht In Worksheets(Detailed.Name).ChartObjects

        With objCht '.Chart
            ThisWorkbook.Activate
            .Copy
            dataFile.Activate
            Select Case i
                Case 1
                    Application.Goto Range("B2")
                Case 2
                    Application.Goto Range("B17")
                Case 3
                    Application.Goto Range("R17")
                Case 4
                    Application.Goto Range("J17")
                Case 5
                    Application.Goto Range("N17")
                Case 6
                    Application.Goto Range("J2")
                Case Else
                    MsgBox "Hmmmm...."
            End Select
            ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
        End With
        i = i + 1

    Next objCht
    ...

Upvotes: 2

Views: 1832

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

For Each iterates a collection from first to last item, in the order they were added.

So either you cut-and-paste (i.e. delete and re-create) every chart object in the order you want them to be iterated, or you name them with a sequence and use a For loop to pull each chart from the ChartObjects collection by name, like in Siddharth's answer.

The only problem is that collections want to be iterated with a For Each loop. You'll pay a performance penalty if you iterate a collection with a For loop.

Upvotes: 2

Brandon Barney
Brandon Barney

Reputation: 2392

The best approach (if you want to assume direct control, and not let your code use the potentially random indices) would be to rename your charts. To do this, go to Home > Find and Select > Selection Pane. From here, you can directly name your charts and then do something like this.

ThisWorkbook.Activate
Dim objCht As Object

For Each objCht In Worksheets(Detailed.Name).ChartObjects

    With objCht '.Chart
        ThisWorkbook.Activate
        .Copy
        dataFile.Activate
        Select Case objChart.Name
            Case "Name1"
                Application.Goto Range("B2")
            Case "Name2"
                Application.Goto Range("B17")
            Case "Name3"
                Application.Goto Range("R17")
            Case "Name4"
                Application.Goto Range("J17")
            Case "Name5"
                Application.Goto Range("N17")
            Case "Name6"
                Application.Goto Range("J2")
            Case Else
                MsgBox "Hmmmm...."
        End Select
        ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
    End With
    i = i + 1

Next objCht

That all said, it does beg the question of why are your charts randomly reordering themselves? Additionally, an approach like this won't be very flexible as you add more charts (you'll have to add the name, and position manually every time).

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149325

Is this what you want? I am assuming that the charts are named sequentially. i.e 1 to whatever number (Charts.Count)

Dim ws As Worksheet
Dim objChart As ChartObject
Dim i As Long

Set ws = ThisWorkbook.Sheets("YourSheetName")

For i = 1 To ws.ChartObjects.Count
    Debug.Print ws.ChartObjects("Chart " & i).Chart.Name

    With ws.ChartObjects("Chart " & i).Chart
        '~~> Do what you want here
    End With
Next i

Upvotes: 4

Related Questions