I need help with VBA code for an ActiveX Button that uses a Slicer to filter a worksheet.

Hello, I need some code that does the following with the triggers listed. I wrote my idea of what the code might look like. Thanks.

User Input:
1. The user selects a record from a Slicer
2. The user clicks an ActiveX Button to navigate to another sheet within the workbook.

Macro:
1. The sheet automatically filters the data to show only the records which apply to the value which the user selected from the Slicer.

 With ActiveWorkbook.SlicerCaches("Slicer_Author")
          .SlicerItems("WHATEVER THE USER CHOSE").Selected = True
          Sheets("Contributor-Dashboard").Select
          ActiveSheet.Range("$A$1:$N$82448").AutoFilter Field:=3, Criteria1:= _
        "WHATEVER THE USER CHOSE"
End With
Contributor-Dashboard-Dummy-Data.xlsm
calyx_terenAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try

Sub Macro
For Each Item In ActiveWorkbook.SlicerCaches("Slicer_Author").VisibleSlicerItems
        strArray = strArray & Item.Value & ","
    Next
    strNames = Left(strArray, Len(strArray) - 1)
    aNames = Split(strNames, ",")

    If UBound(aNames) = 0 Then
        Sheets("DummyData").Range("A1:I100000").AutoFilter Field:=3, Criteria1:=aNames
    Else
        Sheets("DummyData").Range("A1:I100000").AutoFilter Field:=3, Criteria1:=aNames, Operator:=xlFilterValues
    End If
End Sub

Open in new window

Regards
0
 
Rory ArchibaldCommented:
Not for points

FWIW, you don't need the If:

    anames = Split(strNames, ",")
    Sheets("DummyData").Range("A1:I100000").AutoFilter Field:=3, Criteria1:=anames, Operator:=xlFilterValues

Open in new window

0
 
calyx_terenAuthor Commented:
Thanks for your help, Rory. It works. I did make some small changes to have the script open the sheet that it filters and changed the  heading of the script. Ultimately, this is what I used.

Private Sub CommandButton1_Click()
For Each Item In ActiveWorkbook.SlicerCaches("Slicer_Author").VisibleSlicerItems
        strArray = strArray & Item.Value & ","
    Next
    strNames = Left(strArray, Len(strArray) - 1)
    aNames = Split(strNames, ",")

    If UBound(aNames) = 0 Then
        Sheets("DummyData").Range("A1:I100000").AutoFilter Field:=3, Criteria1:=aNames
    Else
        Sheets("DummyData").Range("A1:I100000").AutoFilter Field:=3, Criteria1:=aNames, Operator:=xlFilterValues
    End If
 
    Sheets("DummyData").Range("A1:I100000").AutoFilter Field:=3, Criteria1:=aNames, Operator:=xlFilterValues
    Sheets("DummyData").Select

End Sub
0
 
Rgonzo1971Commented:
Hi,

Why not simply use

Private Sub CommandButton1_Click()
    For Each Item In ActiveWorkbook.SlicerCaches("Slicer_Author").VisibleSlicerItems
        strArray = strArray & Item.Value & ","
    Next
    strNames = Left(strArray, Len(strArray) - 1)
    aNames = Split(strNames, ",")
    Sheets("DummyData").Activate
    Range("A1:I100000").AutoFilter Field:=3, Criteria1:=aNames, Operator:=xlFilterValues
End Sub

Open in new window

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.