Chart title link to slicer

[MimiM]

Hi all

I have searched the forum for Dynamic Chart Title, but all I've found is the standard link to a cell or a formula linked to a cell.

I have a chart based on a pivot table, and a Slicer based on one of the fields, Clinician. I should like the chart title to include the name of the clinician selected in the Slicer.

Is this possible at all please?

Thank you in advance

Mimi

 

[mrshl9898]

If you add Clinician as a filter on the pivot, you could just reference that cell for the chart title. This will change with the slicer selection.

 

[mrshl9898]

Or:

*just record a macro and change the slicer to find the SlicerCaches name

Sub renamechart()


Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Contract_Obligation")  'update
Dim sItem As Excel.SlicerItem
    For Each sItem In cache.SlicerItems
        If sItem.Selected = True Then
        GoTo setname
        End If
    Next sItem
    Exit Sub


setname:
    ActiveSheet.ChartObjects("Chart 2").Activate  'update
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = sItem.Name


End Sub

 

[MimiM]

Thanks - but I'm hopeless at VBA. How would I record the code above, step by step, please? I seem to have recorded the sub under a macro but when I try to run, it comes up with Error 5 - invalid procedure call or argument. Highlights the row where I update the slicer name in the cache. And yes, I have used the name of the slicer in the workbook, "Clinician"

 

[MimiM]

I've now done just that and hidden the pivot table behind the graph - many thanks

 

[mrshl9898]

Sounds sorted, but just to answer your question.

Go to Developer>Record Macro, press alt+F11 to open the VBA editor.

Make a selection on a slicer. You should see code like:

With ActiveWorkbook.SlicerCaches("Slicer_1")        
       .SlicerItems("1").Selected = True
End With

Change

[COLOR=#333333]SlicerCaches("Slicer_Contract_Obligation")[/COLOR]

to

[COLOR=#333333]SlicerCaches("Slicer_1")[/COLOR]

 

[MimiM]

Excellent - will try as soon as I can