-1

I am wondering how can I insert the slicer into the userform after creating the pivot table in excel 2016.

Inserting slicer means clickable slicer, not image..

Could anyone show me some example vba code? Thanks in advance

enter image description here

Yung Lin Ma
  • 121
  • 2
  • 14
  • 1
    What you want can be easily created if you know what to do. [THIS](http://www.vbforums.com/showthread.php?511916-VBA-Working-with-Scroll-Bars-The-most-simplest-way) and [THIS](https://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically/10224992) can get you started :) – Siddharth Rout Aug 04 '19 at 13:26
  • I know exactly how to create button and assign vba to the button. Just couldn't figure out how to insert slicers into userform – Yung Lin Ma Aug 04 '19 at 14:16
  • 1
    Think of those "clickable things" as buttons. simply creste those buttons dynamically :) – Siddharth Rout Aug 04 '19 at 15:43
  • [Here](http://www.tushar-mehta.com/publish_train/xl_vba_cases/1051%20Add%20userform%20items%20on%20the%20fly.shtml) is a similar way to do it :) – Siddharth Rout Aug 04 '19 at 15:45
  • Hi, SR, Thank you for your advice but I did search around before posting here, so please don't share unverified hyperlink as an answer. The site you shared didn't work for me. It is apprecite if anyone could show me the code. Thanks – Yung Lin Ma Aug 05 '19 at 14:16
  • Ok I will let someone else help you then :) – Siddharth Rout Aug 05 '19 at 14:31

1 Answers1

0

You must have achieved what you wanna do by now but anyway...

Create unique data as range or array somehow and put them in ListBox when initialize your form. Or try adding every row into the ListBox while cheking it's not in there yet.

I avoided latter or any counting by copying range to newly created sheet because my data can be large. So I used RemoveDuplicates method.

You can also use Advancedfilter for non-table data.

*adjust ranges to yours

Private Sub UserForm_Initialize()
    Application.DisplayAlerts = False
    Dim i As Long
    ThisWorkbook.Worksheets("Sheet1").ListObjects(1).ListColumns(3).Range.Copy ''my table#1's column3
    Sheets.Add.Visible = False
    With ActiveSheet.Previous
        .Range("A1").PasteSpecial
        .Range("A1").CurrentRegion.RemoveDuplicates 1, xlYes
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            ListBox1.AddItem .Cells(i, 1)
        Next i
        .Delete
    End With
    Application.DisplayAlerts = True
End Sub

Then you can set autofilter with what's/'re selected on the ListBox.

Kantumrobot
  • 97
  • 7