1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'The Vault' started by chirayu, Jul 26, 2016.

  1. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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.

    Attached Files:

  2. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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

    Attached Files:

    Thomas Kuriakose likes this.
  3. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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.

    Attached Files:

    velu_888 and Thomas Kuriakose like this.
  4. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,606
    If you change code in ClearBtn Button like below then it can act like toggle also.
    Code (vb):
    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
    velu_888 and Thomas Kuriakose like this.
  5. chirayu

    chirayu Well-Known Member

    Messages:
    725
    Interesting code but the VBA Slicer already has an Unfilter button which acts like a Select All + Filter anyways. Thanks though.
  6. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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.

    Attached Files:

    velu_888, Luke M and Thomas Kuriakose like this.
  7. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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.

    Attached Files:

    velu_888 and Thomas Kuriakose like this.
  8. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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

    Attached Files:

  9. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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.

    Attached Files:

    velu_888 likes this.
  10. chirayu

    chirayu Well-Known Member

    Messages:
    725
    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

    Attached Files:

    velu_888 likes this.
  11. chirayu

    chirayu Well-Known Member

    Messages:
    725
    Bug Fix v8 identified by GerPlante at the Ozgrid forum

    Attached Files:

    velu_888, sn152 and Thomas Kuriakose like this.
  12. velu_888

    velu_888 New Member

    Messages:
    3
    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........!
  13. chirayu

    chirayu Well-Known Member

    Messages:
    725
    @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?
  14. velu_888

    velu_888 New Member

    Messages:
    3
    Still I'm not able to rectify this, I've uploaded the image of PivotSlicer & Worksheet.

    Attached Files:

  15. chirayu

    chirayu Well-Known Member

    Messages:
    725
    @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
  16. velu_888

    velu_888 New Member

    Messages:
    3
    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
  17. chirayu

    chirayu Well-Known Member

    Messages:
    725
    @velu_888 can you attach a sample file so I can check it

Share This Page