I am working on an interactive report linked to an olap Cube. One of my slicers has 20+ products available to choose from. For ease of use I've created an option box that groups those 20+ products into six categories. I've coded a click event for each option box to filter the products tied to that group if true, and un-filter if false. However, I'm trying to find a better way to handle multiple selections that doesn't involve coding ever single combination of parings (i.e. Fruit and Vegetables). Example below of my code. My thought is through an if statement that adds to the slicer filter if checkbox.value = true or maybe even a loop but I can seem to find the right code. Any help is appreciated.
Here is where I was when I realized I'd have to code all the different combinations with this approach. The more I think about it I'm leaning towards a loop vs. IF/Then.
Here is where I was when I realized I'd have to code all the different combinations with this approach. The more I think about it I'm leaning towards a loop vs. IF/Then.
Code:
Private Sub APDGroup_Click()
Dim WB As Workbook
Set WB = Workbooks("Interactive Map.xlsm")
If APDGroup.Value = False Then
WB.SlicerCaches("Slicer_Product_Groups").ClearManualFilter
End If
If APDGroup.Value = True Then
WB.SlicerCaches("Slicer_Product_Groups").VisibleSlicerItemsList = _
Array( _
"[Products].[Product Groups].[Product Group].&[Auto Entry]")
End If
If DockGroup.Value = True and APDGroup.value =True Then 'Here is where I want to add an if true add option.
WB.SlicerCaches("Slicer_Product_Groups").VisibleSlicerItemsList = _
Array( _
"[Products].[Product Groups].[Product Code].&[DE]", _
"[Products].[Product Groups].[Product Group].&[Auto Entry]", _
"[Products].[Product Groups].[Product Code].&[TR]")
End If
If OtherGroup.Value = True Then
WB.SlicerCaches("Slicer_Product_Groups").VisibleSlicerItemsList = _
Array( _
"[Products].[Product Groups].[Product Code].&[LIFT]", _
"[Products].[Product Groups].[Product Code].&[SP]", _
"[Products].[Product Groups].[Product Code].&[SS]")
End If
End Sub