Turning on/off slicer fields by VBA - compile error

Alarix

New Member
Joined
May 23, 2012
Messages
4
Hi,
Could anyone advise me on possible reason for the compile error below?
I was looking for solution how to replace Pivot table filters by slicers, and intended to have particular values selected on these slicers. I asctually already had manually created the slicers there, so there was dual control over the Pivot.
I found the solution and made the Sub work on the downloaded testing file, but now I cannot make the same code work in my target file.
The message is "Compile error: A module is not a valid type".
The solution I found was second Sub on this link: http://www.databison.com/slicer-vba-code-create-change-or-modify-a-pivot-table-slicer-using-vba/.

The only thing I changed on that module was replacing "Region" with "CoCd" and "West" with "C046", to be able to run it on my macro file and my data, before I develop it further.
The red highlighted statement is the one which prompted the Compile error, I never got any further:
Rich (BB code):
Sub turn_off_on_slicer_field()
Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "CoCd", "CoCd").Slicers
Set k = j.Add(ActiveSheet, , "CoCd", "CoCd", 0, 0, 200, 200)
 i("CoCd").SlicerItems("C046").Selected = True
'You can also use
k.SlicerCache.SlicerItems("C028").Selected = False
'Or
k.SlicerCache.SlicerItems(1).Selected = False
 MsgBox "Turned off C046"
 i("CoCd").SlicerItems("C046").Selected = True
'Or
k.SlicerCache.SlicerItems("C046").Selected = True
 MsgBox "Turned on C046"
End Sub
I tried to delete the manually created slicers with same name, deleted it on all three sheets which were using them. Still no luck.
Appreciate any help,

Alarix
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Do you have a module named SlicerCaches? If so, rename it to something that isn't an Excel or VBA name.
 
Upvote 0
Gosh, it finally dawned on me, after several hours... please delete the thread as the solution is found... I had named the module by wrong name... :(
 
Upvote 0
Rory, thanks a lot. I have in the meantime had a spark of an intelligence and found myself... This thread therefore can not help anyone and can be removed...
 
Upvote 0
We don't delete threads, and this principle could certainly help someone else in the future.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top