Hi there,
I have a chart in excel with 28 different series.
I have a combo box with the 28 different selections and each selection would call a macro to filter the other 27 and display 1 only.
Here is the code is have:
Anyone has any idea how i can shorten this? I dont want to repeat this 28 times!
Thank you!
I have a chart in excel with 28 different series.
I have a combo box with the 28 different selections and each selection would call a macro to filter the other 27 and display 1 only.
Here is the code is have:
Code:
Sub PairDrop_Change()
Dim c As ControlFormat
Set c = Sheets("Report").Shapes("PairDrop").ControlFormat
Select Case c.Value
Case 1: "Macro 1"
Case 2: "Macro 2"
Case 3:
Case 4:
Case 5:
Case 6:
Case 7:
Case 8:
Case 9:
Case 10:
Case 11:
Case 12:
Case 13:
Case 14:
Case 15:
Case 16:
Case 17:
Case 18:
Case 19:
Case 20:
Case 21:
Case 22:
Case 23:
Case 24:
Case 25:
Case 26:
Case 27:
Case 28:
End Select
End Sub
Sub Macro1()
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.PlotArea.Select
ActiveChart.FullSeriesCollection(2).IsFiltered = True
ActiveChart.FullSeriesCollection(3).IsFiltered = True
ActiveChart.FullSeriesCollection(4).IsFiltered = True
ActiveChart.FullSeriesCollection(5).IsFiltered = True
ActiveChart.FullSeriesCollection(6).IsFiltered = True
ActiveChart.FullSeriesCollection(7).IsFiltered = True
ActiveChart.FullSeriesCollection(8).IsFiltered = True
ActiveChart.FullSeriesCollection(9).IsFiltered = True
ActiveChart.FullSeriesCollection(10).IsFiltered = True
ActiveChart.FullSeriesCollection(11).IsFiltered = True
ActiveChart.FullSeriesCollection(12).IsFiltered = True
ActiveChart.FullSeriesCollection(13).IsFiltered = True
ActiveChart.FullSeriesCollection(14).IsFiltered = True
ActiveChart.FullSeriesCollection(15).IsFiltered = True
ActiveChart.FullSeriesCollection(16).IsFiltered = True
ActiveChart.FullSeriesCollection(17).IsFiltered = True
ActiveChart.FullSeriesCollection(18).IsFiltered = True
ActiveChart.FullSeriesCollection(19).IsFiltered = True
ActiveChart.FullSeriesCollection(20).IsFiltered = True
ActiveChart.FullSeriesCollection(21).IsFiltered = True
ActiveChart.FullSeriesCollection(22).IsFiltered = True
ActiveChart.FullSeriesCollection(23).IsFiltered = True
ActiveChart.FullSeriesCollection(24).IsFiltered = True
ActiveChart.FullSeriesCollection(25).IsFiltered = True
ActiveChart.FullSeriesCollection(26).IsFiltered = True
ActiveChart.FullSeriesCollection(27).IsFiltered = True
ActiveChart.FullSeriesCollection(28).IsFiltered = True
ActiveChart.FullSeriesCollection(1).IsFiltered = False
End Sub
Anyone has any idea how i can shorten this? I dont want to repeat this 28 times!
Thank you!