CODEWITHSUNDEEP
excelvba
user8261831
user8261831

Reputation: 504

writing a macro code to turn on and off a series in an excel chart

I have some data in excel that looks like this

Time Ending     NSW1.Price     Black.Coal      Gas
1/01/2011 0:00  30.89335731 32.33667677 41.63653171
8/01/2011 0:00  30.98102854 32.24805366 41.33294734
15/01/2011 0:00 30.73075514 32.11496704 40.76272865
22/01/2011 0:00 30.76027568 30.50381086 36.56215084
29/01/2011 0:00 29.76732763 34.65090389 43.94289058
5/02/2011 0:00  37.76696975 39.97741408 52.7701473
12/02/2011 0:00 37.94918779 38.96889352 50.08463564
19/02/2011 0:00 37.20419977 38.83388594 49.44580279
26/02/2011 0:00 36.87868151 38.64890145 48.77325684

I am trying to create a checkbox that turns on and off series in the graph. For example if I only want a graph of coal and gas but not nsw1price.

I recorded a macro to do this and then I assign it to a checkbox. However my macro doesn't work?

This is the code from the recording:

Sub Macro8()
'
' Macro8 Macro
'

'
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveSheet.ChartObjects("Chart 2").Activate
    Application.CutCopyMode = False
    Range("J17").Select
End Sub

I'm not sure how to fix it?

Any help would be appreciated, thanks.

Edit1:

legend question

Upvotes: 0

Views: 311

Answers (2)

Diederik Sieburgh
Diederik Sieburgh

Reputation: 111

Alright, there are two ways to accomplish this, each with their pluses and minuses.

1) I think the easiest way would be to add a white box that covers the legend item and flip this visible state of the box opposite to the series being visible. ActiveSheet.Shapes("coalRectangle").Visible = msoTrue

2) The next option would rethink the whole process where you would delete and then reinstate each series:

Sub DeleteSeries()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(3).Delete
End Sub

Sub AddSeries()
    ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveChart.SeriesCollection.NewSeries
        .Values = "=Sheet1!L12:L23"
        .Name = "=Sheet1!L11"      
    End With
End Sub

The advantage of the first one is it is simple but depending on the layout of your legend could be tricky if there are many columns. The disadvatnage of the second one is keeping track of the series numbers if users can add or remove many different columns.

Upvotes: 0

Diederik Sieburgh
Diederik Sieburgh

Reputation: 111

You will need to identify the series number you are trying to modify. But this code will flip the visible state of the series. This works for a line graph: 

Sub Macro1()    
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(2).Format.Line.Visible = _
        Not ActiveChart.FullSeriesCollection(2).Format.Line.Visible
End Sub

If you have a bar graph you will need to address the fill as well: 

ActiveChart.FullSeriesCollection(2).Format.Fill.Visible = _
Not ActiveChart.FullSeriesCollection(2).Format.Fill.Visible

Upvotes: 1

Related Questions