Toggle to turn legend on several charts on and off

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

if you are not sure what code you need just record the macro.

+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

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

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

Select allOpen in new window

hth
Rob

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

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

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