[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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.
0
billb1057
Asked:
billb1057
  • 11
  • 7
  • 3
  • +2
4 Solutions
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now