Update Slicer (Choose Multiple Items) from Macro

quixter

New Member
Joined
Nov 1, 2015
Messages
20
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:

Code:
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,300
With J5 containing your comma delimited list, try...

Code:
    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...

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

Hope this helps!
 

quixter

New Member
Joined
Nov 1, 2015
Messages
20
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.

Code:
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
 

quixter

New Member
Joined
Nov 1, 2015
Messages
20

ADVERTISEMENT

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



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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,300
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,991
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top