Using VBA in Excel to Work with Slicers

Marlene Clapp, Director of Institutional Effectiveness, Massachusetts Maritime Academy
 
eAIR has recently published a number of helpful Tech Tips that feature Excel slicers, including Heather Friesen's tip on Excel dashboards as a business intelligence (BI) tool and Jim Fergerson's on simple interactive Excel dashboards. These Tech Tips inspired me to adopt the use of slicers in a recent report. However, in order to simplify my report and make it more user-friendly, I needed to go beyond the standard features associated with slicers in Excel. Visual Basic for Applications (VBA) is a programming language available in all Microsoft Office applications, including Excel. This tip provides a quick look at a process for using VBA in Excel to work with pivot tables with associated slicers. [Note: For those not working in a Windows environment (or sharing documents with others), Tech Tip commenters point out that Excel for Mac does not support slicers. This Tech Tip also does not reflect the use of Power Pivot.] This tip shows how to use VBA to 1) remove filter drop downs without removing field captions, and 2) display the particular items when selecting more than one item in a slicer.  
 
The Visual Basic Editor can be found on the Developer tab in Excel 2010 (and later) versions. If you do not see the Developer tab in your ribbon, go to the File tab and select “Options.” Under Excel Options, click “Customize Ribbon.” Make sure the Develop tab is selected. 
 
Figure 1. Excel Options: Customize Ribbon 
slicer1.1.JPG
Using VBA to Remove Pivot Table Filter Drop Downs 
 
In order to streamline my report’s presentation, I wanted to remove the filter drop downs shown in the various pivot tables (sample table using actual data shown in Figure 2).  
 
Figure 2. Field Captions and Filter Drop Downs 
 
slicer2.1.JPG
The simplest way to remove filter drop downs is to uncheck the box to “Display field captions and filter drop downs” on the Display tab under PivotTable Options. However, this method removes the field captions as well, which does not always produce the most informative display (Figure 3). 
 
Figure 3. Filter Drop Downs and Field Captions Removed 
slicer3.1.JPG
Instead, you can create a macro in VBA to disable (as well as enable) filter drop downs without also clearing field captions. My report included multiple pivot tables for which I wanted to disable all filters, which is reflected in the following code (macro code was adapted from this website). 
 
To disable all filters in all pivot tables on an active worksheet: 
 1stcode.JPG
Where does this code go in your Excel file to work? On your ribbon, select the Developer tab and click on “Visual Basic.” You should see a window similar to that shown in Figure 4. 
 
Figure 4. Microsoft Visual Basic for Applications Window 
 
slicer4.1.JPG
On the menu bar, choose Insert and then click on “Module.” A smaller module window will appear where you can paste the code. The code must be pasted into a module to work. Figure 5 shows a module with the code for disabling all filters in all pivot tables on the active worksheet. 
 
Figure 5. Microsoft Visual Basic for Applications Module 
slicer5.1.JPG

You will need to run the macro to get it to work. To do so, choose “Macros” on the Developer tab on your ribbon. Make sure the “DisableSelection” macro is selected and then click on “Run” (Figure 6). 
 
Figure 6. Macro Window 
slicer6.1.JPG
 
Fiigure 7 shows the result of running the macro. Running the code for the EnableSelection macro would return all table filters.  
 
Figure 7. Filter Drop Downs Only Removed 
slicer7.1.JPG
 
Using VBA to Display Multiple Items Selected in Slicers 
 
The previous code to create a macro involved a Sub (subroutine) procedure in VBA, which is used to perform actions but not return values. A different type of procedure, a Function procedure, is used to return a value and is illustrated in the next example that uses the same dataset. As shown in Figure 7, when multiple selections are made in a slicer, the corresponding filter simply displays “(Multiple Items)” instead of the specific selections. Using a Function procedure, you can modify that so the display is more informative to users. Go back into the Visual Basic Editor (see Figure 4) and again insert a new module (Figure 5). Paste the following code into the module (adapted from this website): 
 
2nd code.JPG
After you create the module, you will need to decide where on your worksheet you would like to display the value(s) returned from the function. You will need to enter a formula to retrieve the value(s). The format for the formula is =GetSelectedSlicerItems followed by the name of the relevant slicer enclosed in quotation marks in parentheses. For example, the slicer in this example is called Slicer_Major and the related formula is as follows: =GetSelectedSlicerItems("Slicer_Major"). To determine the name of your slicer, right-click on it and choose “Slicer Settings.” The name to use in formulas is displayed (see Figure 8). 
 
Figure 8. Finding Slicer Name 
slicer8.1.JPG
Figure 9 shows the formula (as entered in cell B16) and its result, in this case, of displaying the two items selecting in the slicer for Program(s). 
 
Figure 9. Display of Multiple Slicer Items 
 
slicer9.1.JPG
References: 
Note: All data shown in this Tech Tip is publicly available at the Massachusetts Maritime Academy website.   
 
 

 Comments

 
To add a comment, Sign In
Total Comments: 2
 
Gary posted on 10/25/2016 1:42 PM
Thanks for the interesting Tech Tip! VBA for Applications Excel is a powerful way to use Excel, as this tip shows. I'd like to see more submissions in the same vein.
Martin posted on 8/3/2017 9:26 AM
In the GetSelectedSlicerItems function, I believe the iCt counter should only be incremented if the slicer item is selected. Otherwise, the function would always return "All Programs" because lCt would always equal the SlicerItems.count.

If oSi.Selected Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
'ElseIf oSi.HasData = False Then
' lCt = lCt + 1
End If