0

enter image description hereI wrote a code for my data set, that every time I click on a button, it will switch to the Field list that I selected. But the legend on my chart says Total instead of the Field list name. How do change the legend title? I am pretty new to VBA btw.

Thank you. 

ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = "2019 Revenue"

This is the code that I used to change the Chart title. I just need to figure out a way to change the legend title now. I tried to use the Macro Recorder for it, but it seems like the regular chart and pivotchart is different.

Sub Add_Value_MasterCode()

Dim pt As PivotTable
Dim pf As PivotField
Dim SField As String

'Set Variable
  Set pt = ActiveSheet.PivotTables(1)
SField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text


'Remove Existing Fields
For Each pf In pt.DataFields
If pf.Name <> "Values" Then
    pf.Orientation = xlHidden
End If
Next pf

'Add fields that button was clicked for
pt.PivotFields(SField).Orientation = xlDataField

'Set title and color for each fields
If SField = "2016" Then
    ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = "2016 Revenue"
    With ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
    End With
ElseIf SField = "2017" Then
    ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = "2017 Revenue"
    With ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent2
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
    End With
ElseIf SField = "2018" Then
    ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = "2018 Revenue"
    With ActiveSheet.ChartObjects("chart 1").Chart.FullSeriesCollection(1).Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent3
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
    End With
ElseIf SField = "2019" Then
    ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = "2019 Revenue"
    With ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent4
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
    End With
End If

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Phiking
  • 75
  • 11
  • Can you show your button code where you are trying to do this operation? BTW, I find that browsing the excel object model usually helps: https://learn.microsoft.com/en-us/office/vba/api/overview/excel/object-model – rcriii Apr 18 '19 at 15:05
  • 2nd @rcriii's comments. Unfortunately, MS' docs can be a bit _difficult_ to navigate sometimes. Additionally, using the macro recorder to do it by hand one time, then adapting that code to make it more generic works, too. It writes really _bad_ code, but it _will_ tell you what items in the OM you need to access what you want. Clean up the code it outputs (especially remove the unnecessary [`.Select` statements](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)), and you're golden! – FreeMan Apr 18 '19 at 15:10
  • I just add in the code for the button. @Freeman, yes but I can't figure out a way to change the legend title at all. Don't even know how to change it manually. – Phiking Apr 18 '19 at 15:13
  • Select the chart. Right click on it. Click on `Select Data...`. In the `Horizontal (Category) Axis Labels` box, click `Edit`. Type in a new title, type in the cell reference where you want the title to come from, or click on the cell where you want the new title to come from. Do that in the macro recorder and it will show you what to do. – FreeMan Apr 18 '19 at 15:41
  • @FreeMan that the thing, I do not see this option. Could it be because I only have 1 set of value out there? If this was on a regular chart, I can change the legend easily but on a PivotChart, it won't let me. – Phiking Apr 18 '19 at 15:47
  • I'm not sure that I understand the question (because we can't see the chart). you might be able to change the name of the series? `Chart.FullSeriesCollection(1).Name = "What you Want"` – jessi Apr 18 '19 at 16:17
  • Yes, I see now that this is a `PivotChart` (I missed that earlier) - there are _many_ things that you _cannot_ change in a `PivotChart` the way you do in a regular chart. Post a picture of the chart (identifying what you're looking to change) and a picture of the data, as well. (Blank out any sensitive info!!) That will help a lot in determining what can be changed and how to do so. – FreeMan Apr 18 '19 at 16:52
  • Also, `ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = "2016 Revenue"` could become `ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = SField & " Revenue"` eliminating the much of the `If...Then` block. All of the `With ActiveSheet.ChartObjects("Chart 1")...` can be eliminated except for the one line that's different - set it once _outside_ the `If` block to avoid all that code repitition. Additionally, be very aware that `ActiveSheet` can burn you if your code is running against the wrong worksheet. – FreeMan Apr 18 '19 at 16:56
  • Ok just posted a pic, sorry for so many blacked out. But the circle red is what I want to change. Now that you mention that, is it possible to change that legend name? – Phiking Apr 18 '19 at 17:09

1 Answers1

0

Pivot charts seem to be charts with some mysterious connection to the pivot table. Based on the docs, ActiveSheet.ChartObjects("Chart 1").Chart.[FullSeriesCollection](1) returns a series object. You change the legend for the series object by changing its Name. So I think that after your with statements you want:

ActiveSheet.ChartObjects("Chart 1").Chart.[FullSeriesCollection](1).Name = "field list name"

EDIT: I see @jessi got there first.

rcriii
  • 687
  • 6
  • 9