How to adapt the category label when switching "row/column" in a pivotchart?

[Harry Covair]

Hi the forum,

I’m facing with the following problem.

How can I, in VBA, change the category label when, in a pivotchart, I switch the Row/Column?

As illustration, in the attached file, select a “team” in the Team worksheet. A pivotchart is shown.

In the pivotchart sheet, a click on the “Switch Row/Column” button modifies the chart but the category label remains unchanged while the legend is well adapted. See code in "Thisworkbook", Sub btn_SwitchR_C_click() procedure.

Does some member of the forum could help me to solve the problem?

If needed, worksheets should be unhided.

Thanks in advance

Harry

 

[Harry Covair]

I forgot to upload the file

 

[Chihiro]

Quick and dirty:

For x-axis:

ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "XXXX"

For y-axis change (xlCategory) to (xlValue)

 

[Harry Covair]

Hi Chihiro,
Thanks for your answer.
Your solution don't solve the problem. When changing the name with your code, the category title is well adapted but remains when I switch the Row/column again.
I think the category name must be found in the PivotTable but I don't know how.

Regards,
Harry

 

[Chihiro]

Hi Harry,

You can use above to set x & y axis title when you create the chart and store it in hidden cell and then switch the values around.

For switching cells you can see
http://www.excel-easy.com/vba/examples/swap-values.html

 

[Chihiro]

Just had a thought. More of brute force method... but you can switch position of x & y axis title.

Something like

If ActiveChart.Axes(xlCategory).AxisTitle.Orientation = xlHorizontal Then
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    Selection.Left = 3.037
    Selection.Top = 269.015
    Selection.Orientation = xlUpward
    ActiveChart.Axes(xlValue).AxisTitle.Select
    Selection.Left = 320
    Selection.Top = 453.245
    Selection.Orientation = xlHorizontal
Else
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    Selection.Left = 320
    Selection.Top = 453.245
    Selection.Orientation = xlHorizontal
    ActiveChart.Axes(xlValue).AxisTitle.Select
    Selection.Left = 3.037
    Selection.Top = 269.015
    Selection.Orientation = xlUpward
End If

Adjust position as needed.

 

[Harry Covair]

Hi Chihiro,
I found the solution for my pivot chart.

Sub btn_SwitchR_C_click()
If ActiveChart.PlotBy = xlColumns Then
    ActiveChart.PlotBy = xlRows
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = Worksheets("PiTa").PivotTables("PtRes").RowRange.PivotField.Name
    ActiveChart.ChartArea.Select
Else
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = Worksheets("PiTa").PivotTables("PtRes").ColumnRange.PivotField.Name
End If
End Sub

Regards
Harry

 

[Chihiro]

Nice. Glad you did.