Why does the VBA code only changes the chartobject on the activesheet, code runs fine

[Diag]

Hello,

I am working on a piece of code that changes the y-value(min-max) on the axis and fills the colour in the graph.

Each worksheet has the same chart "Chart 1" with different data. With the code i want to "loop" through all the worksheets and change the Y axis value and colour the graph.

Private Sub CommandButton4_Click()

 Dim ws             As Worksheet
 Dim wb             As Workbook
 Dim ch             As Chart
 Dim cht            As ChartObject
 Dim yax            As Axis
 
 Set ch = ActiveSheet.ChartObjects("Chart 1").Chart
  
    For Each ws In ActiveWorkbook.Sheets
        ws.Range("E3:L38,O5:O6,O10:O38,O1,E1,B1,C3:C38,B36:D38,B6:D8").ClearContents
           
        For Each cht In ActiveSheet.ChartObjects
        ActiveSheet.ChartObjects("Chart 1").Activate
        
            With cht.Chart
            With .Axes(xlValue)
                .MaximumScale = 100
                .MinimumScale = 0
            End With
            End With
        Next cht

  'Change first bar chart series fill color
  ch.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(50, 74, 168)
  
    Next
    
    MsgBox "Check if modifications are performed accordingly!!"
    
    Noise.Hide
    
End Sub

For the active sheet the code runs perfectly, The "loop" clearcontents works fine through all sheets.

However, the part of code that changes the axis value and colour doesnt only work on the active sheet. all other sheets remain unchaged.

The code doesn't give any error.

hope someone can assist.

 

[RoryA]

Your loop code explicitly refers to the active sheet. Replace this:

       For Each cht In ActiveSheet.ChartObjects
        ActiveSheet.ChartObjects("Chart 1").Activate

with this:

       For Each cht In ws.ChartObjects

 

[Diag]

Hello Rory

thanks for your reply.

The first problem is solved. All Yaxis are changed.

Now the onlything is the coloutfill of the graph.

the colourfill still doesnt work on all sheets.

thanks

 

[RoryA]

You need:

 cht.Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(50, 74, 168)

and that line should be inside your For Each loop, not after it.

 

[Diag]

Hello Rory,

I have tried to place it inside the "for" loop and tried several things but only the active sheet graph is coloured.

the code doesnt respond to the othe graphs.

Private Sub CommandButton4_Click()

 Dim ws             As Worksheet
 Dim wb             As Workbook
 Dim ch             As Chart
 Dim cht            As ChartObject
 Dim yax            As Axis
 
 Set ch = ActiveSheet.ChartObjects("Chart 1").Chart

    
    For Each ws In ActiveWorkbook.Sheets
       
        ws.Range("E3:L38,O5:O6,O10:O38,O1,E1,B1,C3:C38,B36:D38,B6:D8").ClearContents
    
        
        For Each cht In ws.ChartObjects
        
With cht.Chart
        ActiveSheet.ChartObjects("Chart 1").Activate
        ch.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(50, 74, 168)
 End With
        
   With cht.Chart
            With .Axes(xlValue)
                .MaximumScale = 100
                .MinimumScale = 0
                
   
     End With
 End With
            
          
 
Next cht


Next
    
    MsgBox "Check if modifications are performed accordingly!!"
    
    Noise.Hide
    
End Sub

 

[RoryA]

You didn't change the code as I indicated. This:

 ch.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(50, 74, 168)

needs to be this:

 cht.Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(50, 74, 168)

 

[Diag]

thanks,

all work fine now