Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2007

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Patachoup - 25 May 2007 07:40 GMT
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 - 25 May 2007 09:58 GMT
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

> Hello,
>
[quoted text clipped - 5 lines]
>
> Thx!
Patachoup - 25 May 2007 10:36 GMT
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 !

++

> Try this, but see the notes below -
>
[quoted text clipped - 81 lines]
> >
> > Thx!
Peter T - 25 May 2007 11:45 GMT
> 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

> Thx Peter, it works perfectly !
>
[quoted text clipped - 97 lines]
> > >
> > > Thx!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.