November 03, 2009

DIY Slicers

Better late than never...

After seeing Microsoft’s post about Slicers on their Excel blog, I thought it might be fun to make my own version. Maybe I can’t wait until the official release of 2010?

Anyway, I put together something that would show which items on a pivot table were visible and which were not. I wanted code that would be fairly versatile, as far as I tested (twice!), the code shows each item in each field to the right of the pivot table.It's still a prototype but feel free to use the code if you find it useful.

Here’s how they look. On the right of the pivot table items, there is a simple validation list that allows you to choose from O (visible) or X (not visible).

I should mention that you can drag your slicers around the worksheet just like the real thing. The only thing that you must do is to ensure there are blank rows and columns around each slicer for them to work properly.

You can delete slicers that you don’t want - refreshing the pivot table to show all slicers once again in their default position which is to the right of the pivot table.

Here’s the link :-)

PS I also tried using an ActiveX listbox. While the code did work okay, I noticed some problems with the screen when the code was run – the items did not show up on some PCs until I forced the screen to update by zooming in and out, hence using cells instead. I imagine a little more experimentation might overcome this if anybody wants to try. Have fun!

Posted by andrewe at 06:44