Changing chart and types using VBA - and eliminate the requirement for macros. |VBA & Macros|Excel Forum|My Online Training Hub

Good Morning All....

My Problem!!

I have my excel dashboard working thanks to the advice from the kind and knowledgeable members of the forum.  Special thanks has to go to Velouria for all his help.

Unfortunately, my work has now disabled macros in excel due to corporate requirements so my dashboard no longer functions correctly.

Macros were used to set the chart to combo and set the chart types whenever the pivot table updates, as if any data was missing or selection invalid, it reset the chart type etc..  I’m informed this is an issue with excel and macro’s are the only way to get around it?  

So my Question is …..   Can this be done without a macro and purely VBA??

I have inserted the details below of the code on my worksheet and macro.

Code on Worksheet with Pivot Table.  Worksheet  = "Prod Hub (Pivot& Graph)"

                                                                     Pivot Table =  “Pivot Table1”

Pivot Table changes by slicers on “Site Dashboard” worksheet.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If Target.Name = "PivotTable1" Then

Sheets("Site Dashboard").Activate

Application.Run "'HUB FINALaw.xlsm'!Test2"

End If

End Sub

--------------------------------------------------------------------------------

Test2 Macro

Sub Test2()

'

' Test2 Macro

    On Error Resume Next

    ActiveSheet.ChartObjects("Chart 10").Activate

    ActiveSheet.ChartObjects("Chart 10").Activate

    ActiveChart.ChartType = xlColumnClustered

    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered

    ActiveChart.FullSeriesCollection(1).AxisGroup = 1

    ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered

    ActiveChart.FullSeriesCollection(2).AxisGroup = 1

    ActiveChart.FullSeriesCollection(3).ChartType = xlLine

    ActiveChart.FullSeriesCollection(3).AxisGroup = 1

    ActiveChart.FullSeriesCollection(1).ChartType = xlLine

    ActiveChart.FullSeriesCollection(2).ChartType = xlLine

    ActiveChart.FullSeriesCollection(3).ChartType = xlColumnClustered

    On Error GoTo 0

End Sub

Any help greatly appreciated.