Slicer not working on Chart in VBA, but works after selecting chart data manually

[eric24g]

I created a VBA script that adds charts and slicers to my worksheet, but for some reason they don't work after adding them. Now, once the charts and slicers are added I would go and select on the chart, right click and click "select data" then click ok. Once I do that the slicers work with the chart. I did a macro record on this process and tried the code from there, but still didn't work. What am I doing wrong.

Below is my code for the slicer for vba:

Sub CreatSlicer()
Dim rng As Range
Dim sl As Slicer
Dim sc As SlicerCache
Dim wS As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook

Set wS = Worksheets("Sheet2")
Set sourceSheet = Worksheets("worksheet1")

Workbooks("MyFile.xlsm").Worksheets("worksheet1").Activate
Set sc = wb.SlicerCaches.Add2(ActiveSheet.ListObjects("table1"), "slicer_name")
Set sl2 = sc.Slicers.Add(wS, , "slicer_name", "slicer_name")

Set rng = Range("W2:AC2")
sl2.Top = rng.Top
sl2.Left = rng.Left
sl2.Width = 150 'there are 72 points to an inch or 28.35 points to a centimeter
sl2.Height = 120
Workbooks("MyFile.xlsm").Worksheets("Sheet2").Activate

End Sub

Below is my chart code:

Sub CreateLineChartWIP()
Dim sourceDataRange As Range
Dim sourceDataRange1 As Range
Dim sourceDataRange2 As Range
Dim sourceDataRange3 As Range
Dim sourceDataRange4 As Range

Dim wk As Worksheet
Dim myChart As chartObject
Set wS = Worksheets("Sheet2")

On Error Resume Next
Workbooks("MyFile.xlsm").Worksheets("Sheet2").Activate
ActiveWorkbook.Charts(1).Delete
On Error GoTo 0

Set sourceDataRange1 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C105")
Set sourceDataRange2 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C208")
Set sourceDataRange3 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C367")
Set sourceDataRange4 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C470")


'create a new chart sheet and place it at the beginning of the destination workbook
Dim oChartSheet As Chart

' Set oChartSheet = destinationWorkbook.Charts.Add(destinationWorkbook.Sheets(1))
Set oChartSheet = wS.Shapes.AddChart.Chart
'set the properties for the chart
With oChartSheet
.SetSourceData Source:=sourceDataRange1
.SetSourceData Source:=sourceDataRange2
.SetSourceData Source:=sourceDataRange3
.SetSourceData Source:=sourceDataRange4
.ChartTitle.text = "Name Of Chart"
.ChartType = xlLine
.Axes(xlValue).TickLabels.NumberFormat = "0.0"
.Axes(xlValue).TickLabels.NumberFormat = "#,##0.0"

End Sub

Below is the code for when I used macro recording when selecting the chart, right clicking and selecting "select data" and pressing ok. Manual process worked, but running the code below didn't work for me. What am I doing wrong?

Sub SelectData2()
'
' SelectData2 Macro
'
'
ActiveSheet.ChartObjects("Line Chart WIP").Activate
ActiveChart.PlotArea.Select
End Sub

Thanks.
Sub SelectData2()
'
' SelectData2 Macro
'
'
ActiveSheet.ChartObjects("Line Chart").Activate
ActiveChart.PlotArea.Select
End Sub

 

[Jon Peltier]

This part of your first procedure sets the source data of the chart four times, each statement overwriting the previous one, so only sourceDatarange4 is being used in the chart. And I don't see End With anywhere.

With oChartSheet
.SetSourceData Source:=sourceDataRange1
.SetSourceData Source:=sourceDataRange2
.SetSourceData Source:=sourceDataRange3
.SetSourceData Source:=sourceDataRange4

And all that the SelectData2 recorded macros do is activate a chart and select its plot area. Nothing is done to the chart's data.

 

[eric24g]

Ok, so I just need to remove the the other souceDataRanges that I have and use just one. I must have accidentally forgot to paste the End With because I have it in my code. Also, for the SelectData2 do I need to get its chart data after activating the chart and selecting the plot area. How would I do that in the code. @Jon Peltier

 

[eric24g]

Also, how to the sourceDataRanges get overwritten when the ranges I have are different. When I just have one of them I run it and select the chart data the slicers only work for the sourcedata that I have. Once I add the other three sources back the slicers work manually, but I still can't get the VBA code working.

 

[Jon Peltier]

You cannot read the source data range with VBA. You can read the series formulas, and if you want, construct the source data range from the pieces.

If those are the ranges you want to plot, they are really just the same columns with more or less rows. With a slicer, you can filter another column, and some rows are hidden. The hidden rows by default are not plotted. The trick is getting a slicer to hide the rows you want. And the way you've set it up, there's no easy way to get the different ranges with a slicer, because the first one is the first 104 rows, the second is the first 207, the third is the first 366 rows, and the fourth is the first 469 rows. So each range includes all the prior ranges, while each single slicer selection is exclusive of the other selections.

I would set it up differently, still with a slicer, but with dynamic ranges. I can't spend the time with it now, but I'll keep this browser tab open and come back to it.

 

[eric24g]

Thanks for the update. I'm kind of new with slicers, but understood what you meant. If you do have some time I would like to get some example code to understand it better because there isn't much documentation on getting the slicers to work for the charts. And you're right my pivot tables are only showing a range at a time. If I was to add the slicer on the worksheet where the pivot table is the slicer will work with the pivot tables and I can see the pivot table change when I click the slicer. I'm just having difficulty understanding the slicer connect with the charts. I'll look into dynamic ranges and see what I can find. @Jon Peltier

 

[Jon Peltier]

Nope, slicers don't work with charts. Slicers work with data, and charts work with data. The first rule of charts is to get the data right before you even make the chart. Otherwise you will spend hours trying to make the chart work with bad data.

This exercise combines a few tricks, and it avoids the need for VBA. First we'll build a small Table on another sheet (named Slicer), which will attach to the slicer. There are four choices for the slicer; I've labeled them A through D, but use whatever labels make sense. The slicer is set on the first column, while the second column contains the number of rows for each slicer option. The formula in cell B1 is

=AGGREGATE(4,3,Table1[Rows])

which essentially means compute the amount which consists of the maximum (first argument 4), ignoring subtotals and hidden rows (second argument 3), of Table1[Rows].

When A is selected, only row 3 is visible, so cell B1 contains 104. When B is selected, only row 4 is visible, so B1 contains 207. If A and B are both selected, cell B1 contains the maximum visible value of 207, so it's as if only B were selected. And so on. This Table can be hidden away on another sheet, and the slicer can be placed on the main sheet.

The main sheet, called Display, looks like this. X values are in B2:B470, and Y values are in C2:C470. The chart initially shows all the data, and the series formula looks like this:

=SERIES(Display!$C$1,Display!$B$2:$B$470,Display!$C$2:$C$470,1)

We now will define two Names (aka Named Ranges, aka Dynamic Ranges). On the Formula tab, click Define Name. Enter a name like MyX, select the worksheet Display under Scope, and enter this formula:

=INDEX($B$2:$B$470,1):INDEX($B$2:$B$470,Slicer!$B$1)

Used like this, the INDEX before the colon defines the first cell of the range as the first cell of B2:B470, and the second INDEX defines the last cell of the range using cell B1 f the sheet Slicer, which is the number from the visible row of the Table.

Define another Name, using a name like MyY, with Scope of worksheet Display, and this formula:

=INDEX($C$2:$C$470,1):INDEX($C$2:$C$470,Slicer!$B$1)

Now select the series in the chart. Edit the series formula from what I showed above to this:

=SERIES(Display!$C$1,Display!MyX,Display!MyY,1)

So now when you select an item in the slicer, a different number of rows is computed, so the number of rows in the X and Y values changes, and the chart shows a different number of points.

 

[eric24g]

Thank you so much Jon for taking the time and showing me the steps. I understand more clearly now how slicers work and ill be retrying these steps. I do have to create a dashboard that automates the creation of charts and slicers using VBA for a project, so I think these formulas will be helpful during the process now that I have a better understanding how this process works. @Jon Peltier