Toggle to turn legend on several charts on and off

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Dear Experts:

I got a macro that toggles the legend on several pie charts 'on' and 'off'.

The trouble is that the original font of the legend is NOT retained after the legend has been turned on again (after having been turned off), i.e. the default font takes over.

How can I make sure that the original font of the legend (say 'Verdana') is retained on these turning on/turning off action?

Help is much appreciated.

I attached a sample file for your convenience.

Thank you very much in advance for your professional help.

Regards, Andreas

 Legend-Toggle-PieCharts.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Save the chart as a template, with and without the legend, then use VBA to swap between the two chart templates.

Commented:
if you are not sure what code you need just record the macro.
hi Andreas,

Does this code help?

Option Explicit

Sub Legend_ShowNoShow_v2()
Dim chtObj As ChartObject
    For Each chtObj In ActiveSheet.ChartObjects

        With chtObj.Chart
            '            If .HasLegend Then
            '                .HasLegend = False
            '            Else
            '                .HasLegend = True
            '                .Legend.IncludeInLayout = True
            '            End If

            .HasLegend = Not .HasLegend
            If .HasLegend Then
                With .Legend
                    .IncludeInLayout = True
                    'concept vaguely sourced from: http://www.xtremevbtalk.com/showthread.php?t=261511
                    .Font.Name = "Verdana"
                End With
            End If

        End With

    Next chtObj
End Sub

Open in new window


btw, I recommend that you read this link to provide a different perspective on pie charts: http://www.perceptualedge.com/articles/visual_business_intelligence/save_the_pies_for_dessert.pdf

hth
Rob
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Most Valuable Expert 2011
Awarded 2010

Commented:
+1 broro183 (for the Stephen Few link)
Thanks Teylyn :)

Each time I want to use that link I go straight to your EF signature and then copy & paste!

Rob
Andreas HermleTeam leader

Author

Commented:
Hi rob,

thank you very much for  your swift help. I guess, I should have been more precise in specifying my requirements.

I stated: "How can I make sure that the original font of the legend (say 'Verdana') is retained on these turning on/turning off action?".

The font 'Verdana' is just an example. It could also be 'Arial' or 'Times New Roman', i.e. no matter which font is initally applied to the legend, it should be retained if the legend gets turned on again (after having been turned off).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Commented:
Andreas
if you don't know what font is going to be used, you can either use code to save the chart as a template first and then re-apply that template when the button is clicked again (check for HasLegend)
or you will need to determine the font first and save that somewhere (I think you would need to save it on the sheet in a cell - I suggest a cell you hope is behind one of the graphs.

HTH
Andreas HermleTeam leader

Author

Commented:
Dear both,

Thank you very much for your professional advice.
It works just fine.

Regards, Andreas
hi Andreas,

Thank you for the feedback & points :-)

Here is an example of some code to store the previous/original font. I have used an alternative approach to gyetton by storing the value in a Defined Name instead of a cell - I think each approach is as valid as the other, they are just different.

Option Explicit
Sub Legend_ShowNoShow_v3()
Dim wb As Workbook
Dim chtObj As ChartObject
Dim ChtDefinedName As String
Dim ChtDefinedNameVal As String

    Set wb = ActiveWorkbook
    For Each chtObj In ActiveSheet.ChartObjects

        With chtObj.Chart

            ChtDefinedName = "OriLegendFont_" & .Parent.Name
            If .HasLegend Then
'I don't normally use on error resume next, but in my brief testing it worked for this example (for the situations where the Named Range didn't exist
                On Error Resume Next
                    wb.Names(ChtDefinedName).Delete
                On Error GoTo 0
                ''if you don't want to see the Named Ranges in the excel 2007 Name Manager you can use the next line...
                'wb.Names.Add Name:=ChtDefinedName, Visible:=False, RefersTo:=.Legend.Font.Name
                ''if you do want to see the defined names use this version...
                wb.Names.Add Name:=ChtDefinedName, Visible:=True, RefersTo:=.Legend.Font.Name
                .HasLegend = False
            Else
                .HasLegend = True
                With .Legend
                    .IncludeInLayout = True
                    ChtDefinedNameVal = wb.Names(ChtDefinedName).Value
                    .Font.Name = Mid(ChtDefinedNameVal, 3, Len(ChtDefinedNameVal) - 3)
                End With
            End If

            'code from v2
            '           .HasLegend = Not .HasLegend
            '            If .HasLegend Then
            '                With .Legend
            '                    .IncludeInLayout = True
            '                    .Font.Name = "Verdana"
            '                End With
            '            End If
        End With

    Next chtObj
    MsgBox "done", vbOKOnly + vbInformation, "MACRO COMPLETE"
    'free memory
    Set wb = Nothing
End Sub

Open in new window


hth
Rob

Commented:
that's what I love about EE, learning alternative ways of doing things.
Thanks Rob, I didn't know you could use a name like this.
hi gyetton,

You may find some other little gems on Chip Pearson's page: http://www.cpearson.com/excel/DefinedNames.aspx

Rob
Andreas HermleTeam leader

Author

Commented:
Hi Rob,

this is really great coding! I am very impressed. This forum deserves its name.

The code works just fine. Great job.

Thank you very much.

Regards, Andreas

Commented:
Rob
Thanks it's a long time since I've looked at Chip's site.
I didn't know one could access names through code like this.

Andreas
Sorry to hijack your question
Cool, I'm pleased everyone's happy :-)

Shameless plug...
There are some other good Forums on the web too, here's one that's up & coming: http://www.thecodecage.com

Rob

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial