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.
LVL 2
billb1057Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
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

0
Saurabh Singh TeotiaCommented:
Use this code...
  • Code-1 to loop in selected sheets only.
  • Code-2 to loop on all sheets..
Saurabh...

Dim ws As Worksheet

For Each ws In Application.ActiveWindow.SelectedSheets
MsgBox ws.Name
Next ws

------------------------------------------------------------------------------------------------------

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
MsgBox ws.Name
Next ws

Open in new window

0
billb1057Author Commented:
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

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

RichardSchollarCommented:
Hi


'all sheets in workbook:

Dim Sht As Object
For Each sht In ActiveWorkbook.Sheets

Next sht
'or:
Dim i As Long
For i = 1 to ActiveWorkbook.Sheets.Count

Next i

'all sheets in selection:

Dim Sht As Object
For Each  Sht In ActiveWindow.SelectedSheets

Next Sht

Open in new window

0
billb1057Author Commented:
Oops -- I posted that while your solutions just came in.  I'll read those and try to figure them out -- sorry.
0
billb1057Author Commented:
... will those give an error if a sheet doesn't have a chart on it?
0
Rory ArchibaldCommented:
You could use this:

Sub ColourAllCharts()
Dim wks as worksheet, objChart as Chartobject
for each wks in activeworkbook.Worksheets
   for each objChart in wks.Chartobjects
     chartcolor objChart.Chart
   next objchart
next wks
End Sub


Sub chartColor(cht As Chart)
Dim vntXValues  As Variant, vntValues  As Variant, lngColour As Long, lngBorder As Long, I As Integer
'Recolor chart

    With cht
        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
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
No, they just loop through any charts that are there; if there aren't any, they don't do anything.
0
telyni19Commented:
Hello again,

I would add a sheet variable:
Dim shtTemp as Worksheet

And then you'd use this For Each statement to loop through all sheets:
For Each shtTemp In Application.Worksheets

If you want to just work with sheets that have at least one chart, you'd wrap your code inside the For Each loop in an If statement that tests this way:
If shtTemp.ChartObjects.Count > 0 Then

So here's the whole structure below:
'Subroutine header
Dim shtTemp as Worksheet
'other Dim statements

For Each shtTemp In Application.Worksheets
  If shtTemp.ChartObjects.Count > 0 Then
  'Run the code you have already to work with each chart
  End If   'If there are no charts, the code will skip that sheet
Next shtTemp

'error catching code
End Sub

Open in new window

0
billb1057Author Commented:
Now to reconcile what looks like great but totally different solutions.  :-)
How about points going to whoever could provide a follow up explanation?
0
Rory ArchibaldCommented:
Explanation of what specifically?
0
billb1057Author Commented:
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??
0
Rory ArchibaldCommented:
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.
0
telyni19Commented:
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.
0
billb1057Author Commented:
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.  
0
Rory ArchibaldCommented:
Correct. I'm a great believer in small, single-purpose routines which is why I kept them separate.
0
billb1057Author Commented:
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.
0
billb1057Author Commented:
Thanks for the excellent help on this question.
0
telyni19Commented:
Sorry for the duplications in answers though. My specialty is explanations, not speed, apparently. :)
0
billb1057Author Commented:
Why did it only give an 8.5 rating -- I said A on every thing?
0
Rory ArchibaldCommented:
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. :)
0
billb1057Author Commented:
That is good to know.  I was afraid that the 8.5 rating was a multiplier on the points somehow.
 
0
billb1057Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.