• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Minimum Slicer Value

C

Cantonalives

Member
Hi all,
I have many slicers in a report I have been working on and would like to be able to not worry about users messing the context. I am happy to have multiple items selected or even a single item, however I have one item that must be present whatever the remaining selection is..

I think VB may be the only way forward here. Basically its like having a default slicer item that cannot be deselected. Sadly recording a macro is not much assistance on this, other than identifying the slicer details.

Has anyone managed to introduce such a thing?

Any thoughts much appreciated.


Thanks,


-CL.
 
Thanks for the speedy reply.

I had been looking at that post myself and just could not adapt the code. When I recorded the macro, I got an 'array' which threw me out a bit. Please see below which is similar but not quite similar enough for me to adapt.

Code: 
 ActiveWorkbook.SlicerCaches("Slicer_ProjectName1").VisibleSlicerItemsList = _
        Array( _
        "[Assignment].[ProjectName].&[Project_Must_Be_Selected]")

Sadly I cannot upload due to confidential info and also it is pulling from SQL, so would likely error.

Thanks,

-CL.
 
I should point out that I can get the default to work, but with the above code, it removes all other selections and just has the default.

Thanks,


-CL
 
You will need a macro like:

Sub AddDefaultSlicerValue()
SelectSlicerValue "Slicer_ProjectName1", "Your Default Value", True
End Sub

Which you will need to run when ever the pivot Table changes
You can run it manually by:
Press Alt+F8
select AddDefaultSlicerValue
Ok

What is the Pivot tables name?
 
Thanks Hui.. There are multiple Pivot Tables utilizing this Slicer.

Cannot get the above to work I am afraid, but I am sure it must be close. My assumption is that the
Code: 
ActiveWorkbook.SlicerCaches
is the area where this must be defaulted.

-CL
 
No
I just want the name of a PT so when it changes it will fire the code to add a slicer value
If there are multiple choose anyone
 
I think your going to need to find a way to post a sample file
Copy the file, Replace all the names with AAA, BBB etc, Replace Phone numbers or email addresses as appropriate

It isn't the values that are important to us it is the structure and fields that we can't see without the file
 
You must log in or register to reply here.
Back
Top