asked on

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

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

Calyx Teren

ASKER

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

Rgonzo1971

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