• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Slicer - UserForm version

chirayu

Well-Known Member
Hi Guys,

This is a UserForm version of the VBA Slicer (basic code) thread. Link below:

http://chandoo.org/forum/threads/vba-slicer-basic-code.27395/

It's not 100% the same because it has some extra features such as "Invert" which inverts your criteria selection in the ListBox & applies it to the Pivot.

I only really decided to make this because in my last thread @jeffreyweir posted a link to his vba slicer creation using UserForms and I thought "I'll give that a crack & see how it goes!" - I'll be honest with ya I stole the "Invert" idea from you lol. The code is all mine though ;)

@Luke M thanks for helping me problem solve with the False/True issue & telling me about the Boolean. Here's v1 - hopefully final too, unless you guys have ideas/ find bugs.

@NARAYANK991 & @bobhc have a look too.
 

Attachments

  • Slicer UserForm v1.xlsm
    76.4 KB · Views: 122
Hey Guys,

I was just checking the file when I noticed a pretty big bug, so I'm releasing v2. To replicate & view this bug - do as follows using v1.

Create the POL slicer. Select some items & Filter. Then hit invert. You'll notice that the first time it appears to do nothing & from then on the ListBox selection appears to be the Inverted version of the Pivot every time you click the Invert button.

This was due to the fact that I was Inverting the ListBox selection before running the code; and since the code is using ".PivotItems.Visible = Not .Listbox1.Selected" the 1st invert essentially = the already filtered pivot.

So what I've done is moved the code for inverting the ListBox selection after the Pivot is inverted. I mean I know I could have changed the "= Not" bit to just "=" and it would have done the same job but I wanted to keep that bit in (don't know why, guess I just like it)

Anyway v2 is below

@Hui @Luke M @NARAYANK991 @bobhc @jeffreyweir
 

Attachments

  • Slicer UserForm v2.xlsm
    76.6 KB · Views: 71
Also guys on a side note I've also created an Alternate UserForm version, in which I merged the SlicerMakerFrm & SlicerX UserForms into one. So the ComboBoxes to choose the Pivot & PivotField are part of the form, and the ListBox updates on the fly depending on which PivotField you choose. I was getting annoyed of constantly creating popups so that's why I made this change. Have a look & use whichever you prefer.
 

Attachments

  • Slicer Alt UserForm v2.xlsm
    61.7 KB · Views: 70
If you change code in ClearBtn Button like below then it can act like toggle also.
Code:
Private Sub ClearBtn_Click()

If Me.ClearBtn.Caption = "Clear" Then
    Me.ListBox1.MultiSelect = fmMultiSelectSingle
    Me.ListBox1.Value = ""
    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ClearBtn.Caption = "Sel All"
Else
    For i = 0 To Me.ListBox1.ListCount - 1
        Me.ListBox1.Selected(i) = True
    Next
    Me.ClearBtn.Caption = "Clear"
End If

End Sub
 
Interesting code but the VBA Slicer already has an Unfilter button which acts like a Select All + Filter anyways. Thanks though.
 
Hi All. Thanks @Chihiro & @Luke M for help provided in below thread

http://chandoo.org/forum/threads/vba-pivot-filtering-without-field-use.30561/#post-182433

Also thanks to Ger Plante over at the Ozgrid forums for giving feedback on Slicer Alternate v2.

http://www.ozgrid.com/forum/showthread.php?t=200583&p=774536

Here's V3 slicer- I've only really modified the Alternate version & not the original one. But if anyone wants to make the changes for the original one themselves then be my guest. After all I haven't protected any of the code as I want users to play around with it & develop their understanding into Pivot filtering etc. like I have over the course of my boredom related projects. Plus I'm not particularly interested in developing things & selling them. I just do it because its challenging, so its fun.

So... Here's what's changed since Slicer Alternate V2

Bug fixes:
- Bug when no Pivot is selected & certain buttons are pressed
- Bug when no Field is selected & certain buttons are pressed

New stuff:
- Will highlight existing filtered criteria i.e. in v2 if you filter a field & move onto a different one & come back to the original one, the listbox will not display which filters are currently applied to that field. V3 it will.
- Only adds fields that are currently being used in pivot layout to the field dropdown, so you don't randomly filter things & wonder why pivot doesn't reflect anything
- Re-Slice button i.e. you'll only use this button if you change the layout of the pivot, so move fields in & out of the pivot as this will update the field dropdown list again so it only displays the actively used fields
- Show All button i.e. it unfilters all fields in the selected pivot so you don't need to click the unfilter button on each field you filtered

Just so you know - you can add the form & module to your personal.xlsb files & it should work.
 

Attachments

  • Slicer Alt UserForm v3.xlsm
    55.3 KB · Views: 51
Hi All,

Here's the v4 version. Its just a bug fix. I noticed that when you add multiple PivotFields into the Values bit of the PivotTable & then use the macro it throws an error when trying to filter. Reason is that when there are multiple, the pivot sees them as 1 PivotField called Data & the actual fields essentially act as PivotItems for the Data field. So I bug fixed that bit to prevent "Data" from showing up in the field dropdown box.
 

Attachments

  • Slicer Alt UserForm v4.xlsm
    56.6 KB · Views: 44
Hi All,

Guess whose back with another exciting episode of the Adventures of Slicer Man and Pivot Boy. Lol just kidding. However I have created Alternate version V5. It now features the ability to use Value Filters through the UserForm.

I kept this as a separate segment on the right of the UserForm with its own Filter/ Unfilter button. As the code was quite lengthy and would have been a nightmare to navigate otherwise. The Unfilter button for this bit will only Unfilter the Value Filters. Not all filters applied for that PivotField. To do that use the original Unfilter button on the left.

Check it out
@Luke M @jeffreyweir @bobhc @Hui @Chihiro @NARAYANK991
 

Attachments

  • Slicer Alt UserForm v5.xlsm
    62.4 KB · Views: 34
Hi Guys,

V6 bugfix is already out. GerPlante over at Ozgrid forums found this bug.

Basically there was an issue with populating the DataFieldCombo when you only use 1 PivotField in the Values section.

In that scenario it is not seen as being part of the "Data" header & Orientation remains as 0 which means the macro never reads it as an Active field & it never gets added to the Values side of it.
 

Attachments

  • Slicer Alt UserForm v6.xlsm
    62.2 KB · Views: 18
Slicer Alternate v7

Bug Fixes:
- Change MyVal1 & MyVal2 variable to Long
- Remove the "Unfilter" segment in the Filter & Invert buttons on Left of UserForm so that Value Filter can be applied at the same time
 

Attachments

  • Slicer Alt UserForm v7.xlsm
    62.7 KB · Views: 29
PivotSlicer.JPG Fantastic...........a well organised forum, I got what I want, its working fine regardless one issue, i've pivot filter with date wise display, using this the list box shows date format as 'mm-dd-yyyy" , On my work sheet & Pivot filter s it is dd-mm-yyyy, please how to correct the date format in the listbox1 to dd-mm-yyyy........!
 
@velu_888 I ran the macro on some sample data I created and the dates are showing accurately for me. Have you checked the formatting of the dates in your file?
 
Still I'm not able to rectify this, I've uploaded the image of PivotSlicer & Worksheet.
 

Attachments

  • PivotSlicer_Worksheet.JPG
    PivotSlicer_Worksheet.JPG
    21.1 KB · Views: 59
@velu_888 sorry for the late reply. I was wondering whether its something to do with your system date settings. please also select your dates in your raw data & right click - format cells - custom dd/mm/yyyy
 
Thanks for your kind response, the raw format data is dd-mm-yyyy, only on the slicer list box its displaying mm/dd/yyyy, I've attached the image of both worksheet date display and also on the slicer.PivotSlicer_Worksheet1.JPG
 
Back
Top