• 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 (basic code)

chirayu

chirayu

Well-Known Member
Hi All,

I was bored yesterday so I did some research & made a code for a VBA Slicer.

The reason for this research is that Slicers only work for Excel 2010 & above. Anyone who doesn't have it will not be able to use/ view Slicers. So I did it more of as a personal challenge. I will be continuing my research into this but thought this was a good start & might be useful to other for their development needs.

Please note that this code is very basic. Limitations below:
1) Only works with Report Filter
2) Can only use 1 Report Filter at a time
3) Can only use 1 Criteria at a time for Report Filter
4) Manual intervention required as Pivot Name & Filter Name must be specified in code

Manual intervention bit is this for the two pieces of code below:
Code: 
Fname = "Type of Movement" 'Filter you used
Pvt = "PivotTable1" 'Pivot table you want the slicer for


Must be changed in SliceNDice as well as FilterUpdater
 

Attachments

  • Slicer.xlsm
    48.1 KB · Views: 81
Hi Chirayu ,

I hope you are aware that in a declaration such as :

Dim Fval, Fname, SName, Pvt As String

only the last variable is actually declared as a String type variable ; Fval , Fname and SName are all declared as Variant type variables.

These three Variant type variables will become String type variables only after you make appropriate assignments.

Narayan
 
@NARAYANK991 Thanks. Didn't know that. I always thought that you could either individually declare the variables, or write them all together with a comma & it amounts to the same declaration.

@jeffreyweir Cool project you have. I like the Invert option. Also your posts are quite humorous. The sarcasm is apparent. I think the next iteration of my code would be kind of an overhaul because I'd have to change the images to check how many report filters there are, find their names, and run for each. Also I probably would need to cut out the entire code segment that makes the images etc & possibly use Toggle Buttons & figure out how to incorporate multiple criteria into filter. Gonna be a while lol
 
@jeffreyweir I downloaded the template by Tushar & it looks pretty cool... if a bit complicated. Had a look at the code in the .xlam file & the code is a bit too confusing for me to go through, so I think I'll probably just create it by myself. Too many variables being linked everywhere.
 
@Luke M @jeffreyweir @NARAYANK991

Hi Guys,

I've improved the Macro more so now it can Filter Rows & Columns

Basically when you run the macro it pops up a Userform that asks you for below
1) Pivot Name
2) Filter Name
3) Filter Type (Report Filter/ Row Labels/ Column Labels - Must match the layout in Pivot)

Then the macro creates a "SlicerMacro" sheet in the report you run the macro in, if the sheet doesn't already exist.

The Macro's that actually filter the Pivot (when you click the image) don't hold the details you filled in the first time around when the images were created.
So in order for the Slicer to work, that information needs to be saved somewhere for reference. Thats why the sheet is created & then hidden/visible as necessary.

The macro is still limited in the fact that it can only use 1 Filter per Filter Type at a time.
What I mean by this is that the Report Filter/ Row Labels/ Column Labels can all have a Slicer but can only have 1 each because whenever a new Slicer is made, it overwrites the old information in the "SlicerMacro" sheet.
Making the old Slicer defunct. Simply put you cannot have 2 Slicers for the same Filter Type.

On the plus side you now have more functionality :) you can have 3 slicers:
- 1 for Report Filter
- 1 for Row Labels
- 1 for Column Labels

Also you no longer need to manually define the Pivot Name / Filter Name in the macro because of the UserForm.

Only other limitation apart from the one stated earlier is that it can only filter 1 Criteria at a time, as before. Can't filter multiple.
 

Attachments

  • Slicer v2.xlsm
    63.2 KB · Views: 28
@Luke M @jeffreyweir @NARAYANK991 @bobhc

Slicer V3 is born.

I had a mini brain wave over the weekend. What if instead of naming my shapes by just the Pivot Filter Name, I added Pivot Table Name & added a unique identifier between this i.e. "¬"

And a unique identifier between the Pivot Filter Name & Shape Number i.e. "~".

This way I could use Variables instead of the hidden sheet to pick the Pivot Table Name & Pivot Filter Name directly from the shape itself.

Which would allow for Multiple Slicers for the same Filter Type so you can have more than 1 Slicer for Report Filter/ Row Labels / Column Labels.

Only limitation left now is you can't filter multiple criteria at a time, as before.

Last hurdle left guys. Time for me to move onto either the creation of toggle button as opposed to shapes or move to something similar to Tushar's version, which Jeffrey told me about earlier (but that looks really complicated, was wondering if any of you guys could help me understand it - can't tell if its a userform slicer or not)
 

Attachments

  • Slicer v3.xlsm
    63.9 KB · Views: 25
@chirayu

Indeed, I was looking for same piece of code from last one year.

I am a big fan of Slicer feature. Excel 2007 does not have this feature; this always irritate me.

Just love the way you have made version 4. Hope to see newer version soon!

Regards,
AM:)
 
@chirayu

I am interested with your slicer, hope version 5 will come soon. I don't know how it's work. I am trying records slicer, it does using true and false to show datas. I just imagine that if we use shift key and/or control key to choose multiple selection, save them in a variable then execute it using true and false.

Hope this clue can inspire your code, I am new in vba code, wish I can do it future so.

Regards
 
Hi All, finally had some time to carry on with this little project of mine.

So the toggle button thing was a bust. Works up till creation of button but doesn't edit the names or move the buttons into a list.

But I did find a way to allow multi filter criteria using the shapes version.

Need a little help before I release the final version though.

Basically I need a tiny piece of code that would say if "(All)" is selected as the criteria then for the all shapes that start with the Application.Caller names criteria i.e. it would be something like PivotName¬PivotField* where the * is basically 1,2,3 etc. they should have the two below changes:

Code: 
 ActiveSheet.Shapes(i).Line.ForeColor.RGB = RGB(113, 137, 63)
  ActiveSheet.Shapes(i).Fill.ForeColor.RGB = RGB(155, 187, 89)


Its just so that all the shapes turn green if all is selected to show the user that nothing is unfiltered
 
@Luke M @NARAYANK991 @jeffreyweir @Khalid NGO @dave_yd @ashish mehra

This definitely is the final version. Changed from deselect to select.

The issue with the v5 was that you'd have to individually deselect each option to view the one you want.

v6 version 1st checks if all criteria are selected by default, then deselects all the ones you didn't click & selects the one you did.
Thereafter it checks to see if you already have a selection criteria & then adds any new selections you make on top of it (multi filter criteria).

It will also stop you from removing a criteria if it is the only one selected in the filter (this feature was already there in v5).
 

Attachments

  • Slicer v6.xlsm
    65.9 KB · Views: 29
Hi ,

I do not know whether this is a problem at my end.

After the slicer is created , when I click on any item , an error is displayed.

When I check Application.Caller , it displays the following for every item :

PivotTable1¬Container_Total_CB

whereas the items are all individually named.

Narayan
 
Hi ,

Another problem is that shapes once created are not deleted , which means if the macro is run a second time , the shapes created in the first run remain , which can get confusing.

For example , if the first time the selection was POL , and if the second time the selection is Biz Type , there are selections of both POL and Biz Type displayed.

Narayan
 
@NARAYANK991

Will look into deletion of shapes. seems easy enough, just didn't bother adding as didn't think of that.

Checked my code seems I already added the deletion bit
Code: 
  'Delete already macro assigned images if present
  '-----------------------------------------------
  On Error Resume Next
  For i = 1 To (FilterCount + 1)
  ActiveSheet.Shapes(SName & "~" & i).Delete
  Next

For the error that you're receiving, that is very odd because I added the macro to some live data files I use & I haven't received any issues. Not really sure about that. On mine the Application.Caller displays something like "PivotName¬PivotField~2" etc as the image
 
Last edited:
Guess what I'm back :) I discovered something with this macro a while back that's kept on bugging me ever since I released V6.

The bug is as follows (using v6 file - type of movement field as example):
- Lets say you have 5 criteria in the pivot
- (All),(blank), VENDOR,FCL,LCL
- You click on every criteria except for (All)
- Logically if you click FCL after that, it should deselect it
- However the code sees it as all criteria are selected by default, so it deselects everything else & keeps FCL
- Mainly because in a Pivot (All) is not seen as a Pivot Item (criteria), it is a custom image I created through the code with custom code
- So 4 selected = All selected

So I have fixed the above bug in the final iteration v7 because I had a mini brainwave today & figured out how to fix it.
 

Attachments

  • Slicer v7.xlsm
    66.4 KB · Views: 76
You must log in or register to reply here.
Back
Top