VBA code to toggle hiding a serie (and relative legend) in a chart

[Patachoup]

Hello,

How would you code a macro in VBA to toggle hide/unhide a serie and its
relative legend in a chart.

I already use spreadsheet tricks to make it, but I need a better solution
(no column hide or #N/A stuff ! lol)

Thx!

 

[Peter T]

Try this, but see the notes below -

Sub Test()
Dim clrIdx As Long
Dim lgdLt As Single, lgdTp As Single
Dim cht As Chart, sr As Series, lgd As Legend
' following perhaps arguments passed to the routine
Dim nSrIdx As Long
Static bVis

nSrIdx = 2 ' < change to suit
bVis = Not bVis

Set cht = ActiveChart

With cht.SeriesCollection(nSrIdx)
.Fill.Visible = bVis
If bVis Then
clrIdx = xlAutomatic
Else
clrIdx = xlNone
End If
.Border.ColorIndex = clrIdx

On Error Resume Next
' will error if not a Line type
.MarkerBackgroundColorIndex = clrIdx
.MarkerForegroundColorIndex = clrIdx
On Error GoTo 0
End With

If cht.HasLegend Then
lgdLt = cht.Legend.Left
lgdTp = cht.Legend.Top
End If

' assumes single axis chart
' with all series charttype the same
' might want to disable screenupdating

cht.HasLegend = False
cht.HasLegend = True
Set lgd = cht.Legend

' caters for other series perhaps not visible
With cht.Legend
For i = cht.SeriesCollection.Count To 1 Step -1
With cht.SeriesCollection(i)
If .Fill.Visible = msoFalse _
And .Border.ColorIndex = xlNone Then
lgd.LegendEntries(i).Delete
End If
End With
Next
If lgdLt Then
.Left = lgdLt
.Top = lgdTp
End If
End With

End Sub

The toggle Legend Entry code is only 'relatively' reliable for typical
charts with a single axis and all series' ChartType's the same. For other
types would require a lot more code to get it reasonably reliable.

If border (line) and marker colours are not default automatic, might want to
store the original colorindex's and replace with same instead of
xlAutomatic. Similarly for other Legend and Legendkey properties.

The example includes minimal error handling for testing, just for the
markers, would need more.

Regards,
Peter T

 

[Patachoup]

Thx Peter, it works perfectly !

Just if you could add some code:

* for the macro to detect the chart type and settings for both series and
legend (color, size, border...) because it moved everything in my chart and
the colors changed at first run of the macro

* arguments passed to the routine: can you add the chart name and the serie
(better name than ID num) so I will be able to call your macro from a button


Thx a lot ! I will be able to use it on every single chart then !

++

 

[Peter T]

Thx Peter, it works perfectly !

Though not quite it seems!

Just if you could add some code:

Are you series and Legend formats not using default formats. If so creat a
default chart, then record a macro while changing all the formats as
required. This will give you the syntax of how to check properties before
changing them (though you won't need any of the Select stuff that gets
recorded.

Having got the properties you will need to store them. There are vaious
ways, either with Static variables or at module level. If you are dealing
with several charts it might be easier to create arrays or a collection of
properties for each chart.

In my quick test nothing moved in my chart after resetting the Legend's
position. But charts can have a habit of doing that, if so means storing
position coordinates of chartarea, plotarea, titles, legend, possibly even
axis scale properties. This could all get quite involved.

A different approach might be to temporarily defne your chart as a custom
type. After changing reapply the Custom type then delete it. Again you can
get all the code by recording a macro.

* arguments passed to the routine:

In the example change
Sub Test()
to
Sub Test(cht As Chart, sr As Series, bVis As Boolean)
delete cht, sr & bVis after all the Dim's

delete
nSrIdx = 3 ' < change to suit
bVis = Not bVis
Set cht = ActiveChart

Change
With cht.SeriesCollection(nSrIdx)
to
With sr

(I've probably missed something similar things to delete/change)

In the calling routine something like

Sub ToggleSeries()
Dim cht as Chart, sr as Series
Dim bVis ' if you will want to check existing state of Series before doing
anything
or
Static bVis ' to retain previous setting


set cht = whatever chart
set sr = cht.seriesCollection("mySeries") , or by index number

Call Test(,cht,sr,bVis)


Regards,
Peter T