Filter Slicer with ActiveX CheckBox Loop

phisher12

New Member
Joined
Mar 29, 2014
Messages
24
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.

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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top