PDA

View Full Version : Slicer vba code



Veeru
05-16-2018, 11:12 PM
Hi,

Looking for a code which can be applied on slicers.

as and when we select something on slicer it should automatically take me to data sheet.

Thanks in advance

p45cal
05-17-2018, 03:18 AM
There are no Slicer events that can be used (at least I don't think so), nor does trying to use Worksheet_TableUpdate work here.
In the attached I've done what you asked; I've used Sheet1's Worksheet_Calculate event handler to activate that sheet, but for that to work I needed to put a formula on that sheet which needs to be recalculated when the table changes (see cell R1) - it might be better to put a Total row at the bottom of the table.
But this has the potential to be very irritating for the user, every change switches them back to the data table, so should they want to make more than one change, they have to keep on flipping back and forth between the sheets. In addition, anything else that might cause that sheet to recalculate will also activate that sheet.

This can be made more selective by having a hidden pivot table somewhere and then using the its various pivot table event handlers. It could be made very selective about when the Data table sheet is activated. I haven't written any code for that yet, but come back should it be necessary.

Paul_Hossler
05-17-2018, 06:11 AM
Hi,

Looking for a code which can be applied on slicers.

as and when we select something on slicer it should automatically take me to data sheet.

Thanks in advance

Since it looks like you want to stay on the data sheet, why not just use filters?

You can even get fancy and add a ListBox control to each column

Veeru
05-17-2018, 11:49 PM
Yes that makes no sense...p45cal......can you elobrate Paul...how to use fancy and a listbox here