Categorized | excel macro and vba, pivot table

Slicer VBA Code – Create, Change or Modify a Pivot Table Slicer using VBA

Here is some VBA code that can help you automate working with a slicer in Excel. Slicers, as you would know, are the latest introduction to Excel. As I mentioned in my previous post, they can also be programattically controlled using simple VBA code. Let’s see how.

Slicer Creation using VBA – Basic Steps

Let’s look at the slicer hierarchy – a workbook has slicer caches (accessed the SlicerCaches object), each slicer cache in turn having multiple slicers (one for each pivot table field) and finally each slicer having one slicer object.

1
2
3

Set My_Slicer_Caches = ActiveWorkbook.SlicerCaches
Set My_Slicers = Add(Source, SourceField, [Name])
Set My_Slicer = Add(SlicerDestination, [Level],[Name],[Caption],[Top],[Left],[Width],[Height])

where
My_Slicer_Caches is an object of type SlicerCaches
My_Slicers is an object of type Slicers
My_Slicer is an object of type Slicer
Source is usually the name of the pivot table from which the slicer is being created.
SourceField is the name of the column header of the field for which the slicer is being created.
Name, Caption, Top, Left, Width and Height are used to specify the other parts of the hierarchy.

Imagine a pivot table that shows the sales of a number of product categories in various regions. We will first construct a slicer based on the “Region” field and then gradually move into carrying out other operations in it using VBA. You can extend the same code to any other field of the pivot table – visible or otherwise.

A word of caution though – If you already have already created a slicer of a particular field for one specific pivot table, you may find it difficult to run another code snippet which creates another slicer for the same field without deleting the first one. In such cases you may frequently find the application throwing the dreaded Run-time error ‘1004’: Application-defined or object-defined error. (More about this at the end of the post).

So every time you have to run the another code snippet, you may want to go back to the excel spreadsheet and manually delete the slicer. If the error still persists, goto the slicer button, explicitly create the slicer for that field and then go back, right click the slicer and remove it. The easier option is the delete the slicer at the end of every VBA code snippet by inserting something magical like slicer.delete

So let’s look at how we can create a simple slicer using VBA. In this case, we want to create a slicer based on the “Region” field. So we catch hold of the pivot table, tell the application that we want to create a slicer for the “Region” field and then specify the specific coordinates and the shape in which we want it to be presented to us

How to Create a Slicer Using VBA

1
2
3
4
5
6
7
8
9
10

Sub create_slicer()
Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers
Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200)
Msgbox "Created Slicer"
End Sub

where
Region is the name field that we want to build the slicer for.
My_Region is the name that has been assigned to the slicers object.

Turn Slicer Fields On and Off Using VBA

Once you have created the slicer, the very next thing you would probably want to do with it is to turn on and off various items within the slicer. In our case, the slicer for the “Region” field has four unique items – East, West, North and South.

Let’s see how we can turn on (and off) the item “West” within that pivot field using VBA.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Sub turn_off_on_slicer_fiekd()
Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers
Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200)
i("Region").SlicerItems("West").Selected = False
'You can also use
k.SlicerCache.SlicerItems("West").Selected = False
'Or
k.SlicerCache.SlicerItems(1).Selected = False
Msgbox "Turned off WEST"
i("Region").SlicerItems("West").Selected = True
'Or
k.SlicerCache.SlicerItems("West").Selected = True
Msgbox "Turned on WEST"
End Sub

Slicer Name and Caption Change Using VBA

You can also modify the slicer name and caption. It makes great sense to provide descriptive and unique captions to slicers when you have more than one slicer based on the same field for multiple pivot tables.

1
2
3
4
5
6
7
8
9
10
11
12

Sub change_name_caption_slicer()
Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers
Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200)
k.Name = "Slicer_Name" 'Or use j(1).Name = "Slicer_Name"
k.Caption = "My Caption" 'Or use j(1).Caption = "My Caption"
Msgbox "Changed slicer name and caption"
End Sub

Delete a Slicer Using VBA

So what to do when the slicer has done its job? Dispose it. How. Simple. Slicer.delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Sub delete_slicer()
Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers
Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200)
Msgbox "Created Slicer"
k.Delete
'OR
'j("My_Region").Delete
Msgbox "Deleted Slicer"
End Sub

Change Slicer Position, Height, Width and Style Using VBA

Other things like the height, width, top and left coordinates can be changed – either at the time of creation of the slicer or at runtime. It is also possible to adjust the height and width of the slicer items. In the example below, we first create a slicer, then move the slicer around by changing its top and left coordinates and then finally adjust the slicer row height and the column width.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Sub change_slicer_look_feel()
Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer
Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers
Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200) ' Specify the dimensions here
MsgBox "Created Slicer"

k.Top = 200
k.Left = 200
'OR
'j("My_Region").Top = 200
'j("My_Region").Left = 200

MsgBox "Moved Slicer"

k.Shape.ScaleWidth 0.4, msoFalse, msoScaleFromTopLeft
k.Shape.ScaleHeight 0.6, msoFalse, msoScaleFromTopLeft
MsgBox "Changed Slicers Row Height and Width"

k.RowHeight = 8.4
k.ColumnWidth = 358.4
'OR use the 'j("My_Region") syntax as shown above
MsgBox "Changed Slicer Field Row Height and Width"

k.Style = "SlicerStyleLight3"
MsgBox "Changed Slicer Color"
End Sub

A few more important things. You can sort, toogle header display, filter setting and change other settings of the slicer using VBA. Here is a small code snippet.

Create Slicer Settings with VBA

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Sub change_slicer_settings()
Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer
Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers
Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200)

k.Caption = "Amazing Slicer"
k.DisplayHeader = True
k.SlicerCache.CrossFilterType = xlSlicerNoCrossFilter ' OR xlSlicerCrossFilterShowItemsWithNoData / xlSlicerCrossFilterShowItemsWithDataAtTop
k.SlicerCache.SortItems = xlSlicerSortDescending 'OR xlSlicerSortAscending
k.SlicerCache.SortUsingCustomLists = True
k.SlicerCache.ShowAllItems = False
End Sub

Possible Issues with Slicer Implementation – Creating Duplicate Slicers with VBA

As a member of the shape family, it is entirely possible to duplicate the slicer object. However when one tries to create two slicer objects on the same pivot field (from the same pivot table), the application seems to throw up the dreaded VBA “Run-time error ‘1004’: Application-defined or object-defined error”.

For example, the VBA code shown below is perfectly valid and work fine:

1
2
3
4
5
6

Sub create_duplicate_slicer()
Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region1").Slicers
Set k = j.Add(ActiveSheet, , "My_Region1", "Region", 0, 0, 200, 200)
ActiveSheet.Shapes.Range("My_Region1").Duplicate.Select
End Sub

but if you try to create two slicer objects, that causes an error. We first try creating a slicer on the same slicers object and give each one a separate name (“Region1” and “Region2”). This doesn’t work and the code generates a VBA “Run-time error ‘5’: Invalid procedure call or argument”.

1
2
3
4
5
6

Sub create_duplicate_slicer()
Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region1").Slicers
Set k1 = j.Add(ActiveSheet, , "My_Region1", "Region1", 0, 0, 200, 200)
Set k2 = j.Add(ActiveSheet, , "My_Region1", "Region2", 0, 0, 200, 200)
End Sub

We then try to create each slicer from a two different slicers object The following code generates a VBA “Run-time error ‘1004’: Application-defined or object-defined error”

1
2
3
4
5
6
7

Sub create_duplicate_slicer()
Set i = ActiveWorkbook.SlicerCaches
Set j1 = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region1").Slicers
Set j2 = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region2").Slicers
Set k1 = j1.Add(ActiveSheet, , "My_Region1", "Region1", 0, 0, 200, 200)
Set k2 = j2.Add(ActiveSheet, , "My_Region2", "Region2", 0, 0, 200, 200)
End Sub

I am not too sure whether that’s a gap in my understanding of the slicer dynamics or a bug – though I think it would likely be the former.

You can download the worksheet with vba code for slicers here or click on the button below:

Prevent Users Ctrl Break (ing) Your VBA Code During Execution

Multithreaded VBA – An Approach To Processing Using VBScript

VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code

Treemap in Excel – Coming Soon

VBA Select Case – Using VBA Select Case Statement in Excel

Control Chart in Excel Using VBA – Six Sigma Control Chart Code & Software

What Do You Think ?

Comments and Trackbacks

Andy May wrote:

The information is great, I just would like to manipulate slicers that already exist on the sheet rather than create them on the fly. Also I don’t know how to call VBA code when the sheet opens.

Regards

Andy

March 21st, 2011 at 9:23 pm
craig wrote:

I downloaded your workbook and stepped through the code. Thanks for that…big help.

However, I have a question unrelated to the slicers.

How did you hide gridlines on the first tab? I see in the View tab that Gridlines are checked and this is a Normal view. I didn’t realize there was another way to do that.

Thanks,

Craig

May 22nd, 2011 at 8:31 am
Rich wrote:

Pretty cool stuff.

January 10th, 2013 at 6:22 pm
Bill McCormick wrote:

This rocked. Thank you very much!

July 31st, 2013 at 12:17 pm
arjo wrote:

I have a template with 4 slicers. After updating the source data, I noticed that the slicers aren’t updated. for example: in my slicer market, there should be one more value “jumbo groep”.

it is in the source data, i’ve refresh pivots using vba: thisworkbook.refreshall but it doesn’t seem to refresh the slicers. Which code do I have to use to refresh my slicers?

December 2nd, 2013 at 4:38 am
Clive wrote:

Thank you for the code. I really wanted a floating slicer that moved down the worksheet as I scrolled. Alternatively…….. Slicer is called “Years” and have column “A” blank. To move the slicer to the required row, double-click on the row number in column “A”.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range(“A:A”)) Is Nothing Then

Dim R As Range
Set R = Target
With ActiveSheet.Shapes(“Years”)
.Top = R.Top
End With

End If

End Sub

The above code was added to the worksheet that contained the pivot table.

Regards
Clive

November 10th, 2014 at 4:38 pm