CODEWITHSUNDEEP
vbaexcel
Jae Carr
Jae Carr

Reputation: 1225

Making a particular series (line) in an excel-chart invisible

So I have a graph that has 6 series on it and I want to be able to make each series visible or invisible using a set of ActiveX check boxes. The check boxes seem to be working fine so far, but I can't figure out how to make an individual series invisible. Is there a property of the series that I can use to make it not visible? Google and Microsoft help aren't turning it up for me.

(Granted, that doesn't mean it isn't there...)

Any and all help is appreciated.

Upvotes: 3

Views: 24576

Answers (5)

user4825591
user4825591

Reputation: 11

In Excel 2013, to make a serie invisible, taking it also out of the legend etc, use the filter option, . Vba Example:

The Following code Hides / Restores the series by filtering it Out/In of the chart. ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).IsFiltered = True / False

This is equivalent to manually filtering a series in/out using the Ribbon command Design + Select Data, (which is available when the char is selected).

All series options, like dashes colors, markers etc.. are restored unchanged.

Upvotes: 1

Wheelie22
Wheelie22

Reputation: 1

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(linenumber).Select
Selection.Format.Fill.Visible = msoFalse

This works great, select the chart than i have 24 lines, with a listview with checkboxes and with 24 entries. Click on 1 and it disapears, click again and its back. You alsow see the line dis- and appear in the legend.

Upvotes: 0

Anirudh Ramanathan
Anirudh Ramanathan

Reputation: 46728

The above 2 did not work for me, in case of ChartType = xlLineMarkers. What did work was:

For Each mySeries In .SeriesCollection
    With mySeries
        .Border.LineStyle = xlNone
        .MarkerStyle = xlNone            
        .MarkerBackgroundColorIndex = xlColorIndexNone
    End With
Next

However, the legend does still display the line. It is necessary to delete that entry separately.

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27239

Update

Wait, I remember that I have come across this before in the past. A good way to hide the data you want is to hide the actual rows / columns of data being charted. So your ActiveX control won't touch the chart, but rather hide / unhide the columns / rows of chart data.

You will just need to set up your chart data in such a way that it doesn't interfere with any other data that needs to be shown.

Leaving my original answer for anyone's benefit...

I just recorded a macro and played with it ...

Got this great result:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select

'Turn it on
Selection.Format.Line.Visible = msoTrue
'Turn it off
Selection.Format.Line.Visible = msoFalse

This is eqiuvalent to selecting No Line in the Line Color section of the Format Data Series dialog box.

Upvotes: 6

Aaron Clark
Aaron Clark

Reputation: 21

When I get stumped in Excel, I always try recording a macro, a la WWBGD? (What Would Bill Gates Do?)

Here's the result for a bar chart where I set the fill to "None":

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
Selection.Format.Fill.Visible = msoFalse

Does that help?

Upvotes: 2

Related Questions