Search code examples
vbaexcelgraphdashboardexcel-2013

Excel VBA code to change pivot chart type

I'm looking for a code to create a button which upon clicking will change the pivot table chart from a bar chart to a line graph. I want the button to be at the bottom of the graph so the user can choose how they can see the data at the click of a button.

Does anyone know the code for this? Ideally a plug and play solution as I'm a beginner to VBA

Thanks

Solution

  • You had two sub routines, but only one end:

    Private Sub CommandButton20_Click()

  ActiveSheet.ChartObjects("Chart 60").Activate
  ActiveChart.ChartType = xlLine

End Sub

    Works for me.

    You can also assign a macro to a chart so something like:

    Sub ChangeMe()

ActiveSheet.ChartObjects("Chart 60").Activate

    If ActiveChart.ChartType = xlLine Then
            ActiveChart.ChartType = xlBarClustered
                Else
                ActiveChart.ChartType = xlLine
    End If

End Sub

    might be worth investigating.