Update Slicer (Choose Multiple Items) from Macro

[quixter]

Hi All,

Currently I have a system in place that will update a slicer based upon ONE value that is chosen from a drop down box.

See code below:

Sub DropDownValue_Rev()


Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Dim PT As PivotTable
Dim wb As Workbook
Dim ws As Worksheet


Set wb = ThisWorkbook


    For Each ws In wb.Sheets
        For Each PT In ws.PivotTables
            PT.ManualUpdate = True
        Next PT
    Next ws


    Dim dbWS As Worksheet
    Dim lbText As String
    
    Set dbWS = Sheets("Dashboard")
    
    With ActiveSheet.Shapes("DropDownRev").ControlFormat
        lbText = .List(.Value)
    End With
    
    dbWS.Range("J5") = lbText
    
    Dim revSel As String


    revSel = dbWS.Range("J5")


    Dim item As SlicerItem


    wb.SlicerCaches("Slicer_Revenue_Type").ClearManualFilter
     
    For Each item In wb.SlicerCaches("Slicer_Revenue_Type").SlicerItems
    If item.Name = revSel Then
    item.Selected = True
    Else
    item.Selected = False
    End If
    Next item


    For Each ws In wb.Sheets
        For Each PT In ws.PivotTables
            PT.ManualUpdate = False
        Next PT
    Next ws


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic


End Sub

However, I now need to be able to select multiple items from a slicer. For example - my field is RevType. Instead of choosing just Budget, I want to be able to choose Budget AND Actual.

So in response to this I then created a Userform where a user can select multiple items and those values will be concatenated and show in a different cell (i.e. Actual, Budget).

How do I change my code to look through the cell with the concatenated items and manipulate my slicer to correspond to those items?

Thanks for any help.

 

[Domenic]

With J5 containing your comma delimited list, try...

    Dim vItems As Variant
    Dim vMatchVal As Variant
    
    vItems = Split(dbWS.Range("J5").Value, ",")
    
    With wb.SlicerCaches("Slicer_Revenue_Type")
        .ClearManualFilter
        For Each item In .SlicerItems
            vMatchVal = Application.Match(item.Name, vItems, 0)
            If IsError(vMatchVal) Then
                item.Selected = False
            End If
        Next item
    End With

If your list is delimited by a command and a space (ie. Actual, Budget), change the delimiter for the Split function accordingly...

 vItems = Split(dbWS.Range("J5").Value, [COLOR=#ff0000]", "[/COLOR])

Hope this helps!

 

[quixter]

Thank you so much Domenic! The above code is extremely helpful.

The only issue I have now is that for some reason the code is selecting the slicer item from my previous choice. So for example, If I originally have Actual in my J5 cell, then I choose Actual, Budget. The Slicer will then filter based on Actual as opposed to Actual, Budget. Would you happen to know why this occurs?

I don't know if this helps, but below is my code for the user form that spits out what items to slice by.

Private Sub OkRev_Click()


    gCellCurrVal = ""
    For ii = 0 To ListBoxRev.ListCount - 1
      If Me.ListBoxRev.Selected(ii) = True Then
        If gCellCurrVal = "" Then
          gCellCurrVal = Me.ListBoxRev.List(ii)
        Else
          gCellCurrVal = gCellCurrVal & ", " & Me.ListBoxRev.List(ii)
        End If
      End If
    Next ii
    
    UserFormRev.Hide
    
    Call DropDownValue_Rev2
    
End Sub

 

[Domenic]

Can you post your code for DropDownValue_Rev2 ?

 

[quixter]

It's essentially the same code, but with your adjustments. I also changed the cell reference to I3. Below is the edited code:

Sub DropDownValue_Rev2()
 
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual




    Dim PT As PivotTable
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook




    Dim dbWS As Worksheet
    Dim lbText As String


    Set dbWS = Sheets("Dashboard")
    
    Dim vItems As Variant
    Dim vMatchVal As Variant
    Dim item As SlicerItem
    
    vItems = Split(dbWS.Range("I3").Value, ", ")
    
    With wb.SlicerCaches("Slicer_Revenue_Type")
        .ClearManualFilter
        For Each item In .SlicerItems
            vMatchVal = Application.Match(item.Name, vItems, 0)
            If IsError(vMatchVal) Then
                item.Selected = False
            End If
        Next item
    End With

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic




End Sub

Thank you

 

[Domenic]

It looks like while you've concatenated the values from your listbox and assigned to a variable, you haven't actually copied the concatenated values to cell I3. And so cell I3 doesn't get updated.

 

[quixter]

Thank you so much for your help! I really appreciate it.