Pivot Table/Slicers and VBA UserForm Combo box

[gw92X]

Hello,

I am using the below code to populate my combo box using values from a slicer:

Private Sub UserForm_Initialize()
Dim SLSP As SlicerItem
Me.ComboBox1.Clear
For Each SLSP In Sheets("Sheet1").PivotTables("PivotTable3").Slicers("Firm Name").SlicerCache.SlicerItems
Me.ComboBox1.AddItem SLSP.Name
Next SLSP
End Sub

When I filter my pivot table, my slicer changes it's values and only shows the required values that have data as per the filter. (I've hidden values with no data due to the filter. If I unhide the values, they are sort of faded/greyed out in the slicer)

When I run the code, the combo box shows the filtered values first and then all other values that are hidden.

Is there any way I can get the combo box to only show the filtered values and not all the values?

I hope that makes sense. Sorry if it doesn't, I am new to VBA, pivot tables and slicers!

 

[Worf]

Welcome to the Board


Please test this:

Private Sub UserForm_Initialize()
Dim SLSP As SlicerItem, pt As PivotTable
Set pt = Sheets("Sheet2").PivotTables("Pivot27")
Me.ComboBox1.Clear
For Each SLSP In pt.Slicers("CR Carrier").SlicerCache.SlicerItems
    If SLSP.Selected And SLSP.HasData Then Me.ComboBox1.AddItem SLSP.Name
Next
End Sub