Setting charts on multiple sheets invisible vba

[kywe665]

I have about 9 different sheets, but they all will have 4 types of graphs (totals, comparison, bydate, trend). I am writing vba to conditionally show one type of graph on every sheet. For example, if I want to show the totals graphs, I want all of my sheets in the workbook to update. I can already make the graphs go invisible and visible on one sheet, but I would like to do so on all sheets. Here's my code now:

Sub UpdateGraph()
 Sheets(".graphManager").ChartObjects("Totals").Visible = False
End Sub

I want to be able to do this on all sheets so I tried something like this:

Sub UpdateGraph()
 Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Visible Then ws.Select (False)
ActiveWorksheet.ChartObjects("Totals").Visible = False
    Next
End Sub

But no luck. I do not want to manually type all the sheet names into an array because I may add more sheets in the future and don't want to keep changing the code. How can I loop through all sheets and set a graph named "Totals" to invisible? Or can I just set all graphs in the workbook named "Totals" to invisible without looping through the sheets? Thanks!

 

[AlphaFrog]

Try something like this..

[color=darkblue]Sub[/color] UpdateGraph()
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] Worksheets
        ws.ChartObjects("Totals").Visible = [color=darkblue]False[/color]
    [color=darkblue]Next[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

If the sheet doesn't have a "Totals" chart, it ignores the error.

 

[kywe665]

Try something like this..
Code:

Sub UpdateGraph() Dim ws As Worksheet On Error Resume Next For Each ws In Worksheets ws.ChartObjects("Totals").Visible = False Next On Error GoTo 0End Sub
If the sheet doesn't have a "Totals" chart, it ignores the error.

Excellent! On another forum someone also suggested this modular function:

Sub UpdateGraph(graphType As String)
 Dim ws As Worksheet
    For Each ws In Sheets        For Each co In ws.ChartObjects
            '''Turn off all charts on the sheet first'''
            ws.ChartObjects(co).Visible = False
        Next
        '''Turn on the one chart type we want'''
        ws.ChartObjects(graphType).Visible = True
    Next
End Sub

 

[AlphaFrog]

You're welcome.

Please read this about cross posting.

Posting Rules #24
Cross-posting is the term we use when a person has posted the same question to multiple online forums. While there is nothing actually "wrong" with this, it is asked that you at least mention that the question has been posted elsewhere, including a link to the thread on the other forum. There are many people who post at several different forums; threads are cross-posted without stating it are easily identified and pointed out as such.

Link: A message to forum cross posters