asked on

Excel - For Each Loop to activate each sheet or selected sheets

This is a follow-up to a prior solution which shows how to loop through all of the charts on a sheet.
Now, I'm wondering what syntax & code can be used to loop through all of the sheets in a workbook.
(There are two questions -- how to loop through all Sheets, and how to loop through selected sheets).

Here's the beginning of the Code ...

'------------
Sheets("chart").Activate
For Each chtobj In ActiveSheet.ChartObjects 'This is the critical statement to loop through each of the charts
With chtobj.Chart 'The chart object contains the actual chart to work with'
'The rest of the code should work as written, provided the With statement is set up correctly'
vntValues = .SeriesCollection(1).Values
'-------------------

Would I add something like:
For Each .Sheets 'not sure of the syntax here.
Sheets("chart").Activate
... then the code that loops through the charts
... then Next '???

Secondly, to loop through just the sheet names that have charts I wonder if it is something like:
For I = 1 to Unbound(Sheets)
IF Sheet ("has a chart on it")
Then,
Do the code for the chart objects
Else
Next Sheet

Another possibility, I guess, would be to put the names of the sheets with charts on them

For I = 1 to Unbound(Sheets)
IF ActiveSheet.Name = "Sheet_with_Graphs1" or "Sheet_with_Graphs2" Then
Sheets("chart").Activate
For Each chtobj In ActiveSheet.ChartObjects
With chtobj.Chart
vntValues = .SeriesCollection(1).Values
... etc.
Next I

Thanks for your help with this.
I'm trying to figure out how to Loop through the processes and where the place the commands.

Rory Archibald

You don't need to activate anything:

Dim wks as worksheet, objChart as Chartobject
for each wks in activeworkbook.Worksheets
   for each objChart in wks.Chartobjects
     ' do something
   next objchart
next wks

Open in new window

SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billb1057

ASKER

I attached more code because I'm not following this.
In order to Loop through all of the charts, where does the For I = 1 To UBound(vntValues) go?
In the code attached, it already selects each ChartObject by name ("Chart1") then activates it, then applies formatting.
Does the For Each replace the line:
With Sheets("chart").ChartObjects("Chart 1").Chart ????

Sub chartColor()
Dim vntXValues  As Variant, vntValues  As Variant, lngColour As Long, lngBorder As Long, I As Integer
'Recolor First chart
Sheets("chart").Select
ActiveSheet.ChartObjects("Chart 1").Activate
    With Sheets("chart").ChartObjects("Chart 1").Chart
        vntValues = .SeriesCollection(1).Values
        
        For I = 1 To UBound(vntValues)
            Select Case (vntValues(I))
                Case 0.5196 To 1
                    lngColour = vbGreen
                    lngBorder = 50
                Case 0 To 0.5195
                    lngColour = vbRed
                    lngBorder = 3
                            
            End Select
            .SeriesCollection(1).Points(I).Interior.Color = lngColour
            .SeriesCollection(1).Points(I).Border.ColorIndex = lngBorder
        Next I
End With

Open in new window

SOLUTION

RichardSchollar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billb1057

ASKER

Oops -- I posted that while your solutions just came in. I'll read those and try to figure them out -- sorry.

billb1057

ASKER

... will those give an error if a sheet doesn't have a chart on it?

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

No, they just loop through any charts that are there; if there aren't any, they don't do anything.

SOLUTION

telyni19

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billb1057

ASKER

Now to reconcile what looks like great but totally different solutions. :-)
How about points going to whoever could provide a follow up explanation?

Rory Archibald

Explanation of what specifically?

billb1057

ASKER

Rorya's looks the simplest and cleanest -- plus it is applied to the code. But a lot was deleted -- will each chart be updated without Activating the sheet??

Rory Archibald

Yes. There is almost never a need to activate or select anything in code and it's faster and more efficient to avoid it, and just manipulate the object directly.

telyni19

rorya's solution is basically the same as mine, except he moved the code to change one chart into a separate function. And he's apparently faster at coding it. :)

It's rare that you actually need to activate anything while running code. That code loops through each sheet and then loops through each (if any) charts. No activation needed, as rorya said. The Worksheets collection automatically contains all worksheets in the file, and the Charts collection automatically contains all charts in the specified sheet.

billb1057

ASKER

Rorya - ok, I got it. You created another Sub which loops through the workbook, and then that references the Color routine, right? That looks good - -and you were first.

Rory Archibald

Correct. I'm a great believer in small, single-purpose routines which is why I kept them separate.

billb1057

ASKER

Point total increased to give Rorya full points, but split the rest for other valuable expert help. (I'm surprised about the number of different ways to code this).
Telyni -- very helpful explanations again, many thanks.

billb1057

ASKER

Thanks for the excellent help on this question.

telyni19

Sorry for the duplications in answers though. My specialty is explanations, not speed, apparently. :)

billb1057

ASKER

Why did it only give an 8.5 rating -- I said A on every thing?

Rory Archibald

I'm not sure anyone understands how the ratings work. It makes no odds to those answering though - the only things that affect us are the points and the grade. :)

billb1057

ASKER

That is good to know. I was afraid that the 8.5 rating was a multiplier on the points somehow.

billb1057

ASKER

A related question is coming shortly (an easy one I think - so lower points) -- so Experts, keep your engines running and get ready for the green light.

Meanwhile, thanks again.