0

I have a problem with a sub which correctly assigns formatting to a chart sheet - but only with launched with the chart visible. Otherwise it applies only one last piece of around 10 formatting steps indicated.

Details: I've got a workbook composed of a few dozens of visible data sheets (along with a few invisible ones) and several visivle chart sheets (sheets containing solely a chart) scattered across the file. I want to assign certain formatting to each visible data sheet and chart. For that reason I created one sub to format sheets, another - for charts. These are called in by a general sub which checks name of each sheet in the workbook, based on which it's discerning data sheets from the graphical ones.

Here's the general sub to launch the other two

Sub Total_update()
    Dim Shtnm As String
    For i = 1 To ActiveWorkbook.Sheets.Count
        Shtnm = ActiveWorkbook.Sheets(i).Name
        If Left(ActiveWorkbook.Sheets(i).Name, 1) = "P" Then
            Call Number_update(Shtnm)
        ElseIf Right(ActiveWorkbook.Sheets(i).Name, 1) = ")" Then
            Call Chart_update(Shtnm)
        End If
    Next i
End Sub

The Number_update sub works flawlessly.
Here's the problematic chart_update sub

Sub Chart_update(Shtnm As String)   

Dim iTested As Integer

iShapes = ActiveSheet.Shapes.Count
iTested = 0

Charts(Shtnm).Activate

ActiveChart.Axes(xlCategory).Format.Line.ForeColor.RGB = RGB(0, 0, 0)  
ActiveChart.Axes(xlCategory).Format.Line.ForeColor.RGB = RGB(1, 1, 1)  

'(several other formatting commands in For loops)

iTested = 2

ActiveChart.SeriesCollection(1).Select                                      
For k = 1 To iTested
    ActiveChart.SeriesCollection(1).Points(k).Select                        
    Selection.Format.Fill.ForeColor.RGB = RGB(3, 3, 3)    'Recoloring certain points only from the 1st series
Next k

ActiveChart.SeriesCollection(2).Select                    'Selecting 2nd series
Selection.Format.Fill.ForeColor.RGB = RGB(4, 4, 4)  
End Sub

Only the last command is implemented even if launched from outside the chart sheet. All the others require manual Alt+F8 sub launch from given chart sheet

Please let me know what am I missing here?

braX
  • 11,506
  • 5
  • 20
  • 33
Piotr Janczewski
  • 11
  • 1
  • 1
    This `iShapes = ActiveSheet.Shapes.Count` is probably the issue. It uses `ActiveSheet` which might not be the chart sheet but any sheet the code is started at. Also I recommend instead of `Charts(Shtnm).Activate` and `ActiveChart` use `Charts(Shtnm)` directly by replacing every `ActiveChart` with `Charts(Shtnm)`. Then you don't need to activate it. – Pᴇʜ Mar 26 '21 at 15:11
  • 1
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Same can be used to get rid of `ActiveSheet` and `.Activate`. Try to get rid of all `ActiveSheet` and `ActiveChart` and your problem will disappear. – Pᴇʜ Mar 26 '21 at 15:13
  • 1
    @Pᴇʜ thanks for hint, it was helpful. I replaced every ActiveChart with Charts(Shtnm), but Charts(Shtnm).Activate also proved to be necessary for the code to work. – Piotr Janczewski Mar 29 '21 at 12:57
  • You can post your solution as answer and accept it as solution after one day if that solved your issue. – Pᴇʜ Mar 29 '21 at 12:59

0 Answers0