• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1452
  • Last Modified:

slicer magic

Is there a VBA to multi select slicers?
Like there is a slicer group called [Rep]. I can now go and hit ctrl + the slicer values to select multiple values within the slicer group. Can the same be done with VBA – multi select slicers withn a slicer group?
slicerMagic.xlsm
0
Rayne
Asked:
Rayne
  • 4
  • 3
2 Solutions
 
Steven HarrisPresidentCommented:
If you are wanting to pre-define selected items, you can use:

With ActiveWorkbook.SlicerCaches("Slicer_Rep")
        .SlicerItems("Andrews").Selected = True
        .SlicerItems("Gill").Selected = False
        .SlicerItems("Howard").Selected = False
        .SlicerItems("Jardine").Selected = True
        .SlicerItems("Jones").Selected = False
        .SlicerItems("Kivell").Selected = False
        .SlicerItems("Morgan").Selected = True
        .SlicerItems("Parent").Selected = True
        .SlicerItems("Smith").Selected = False
        .SlicerItems("Sorvino").Selected = True
        .SlicerItems("Thompson").Selected = False
End With

Open in new window

0
 
RayneAuthor Commented:
thank you ThinkSpaceSolutions,
I was thinking of multi-selecting all the values like looping through the slicers in vba ...is that possible?

Thank you
0
 
Steven HarrisPresidentCommented:
...multi-selecting all the values like looping through the slicer

Can you explain that a bit further?

Are you wanting to loop through each item in the slicer and enable it?  This would be the same as setting each item to 'True' at one time unless you introduce some other functions that need to happen as well.

Are you wanting to iterate through each item (appending it to the last one) in order to make a visual 'demo'?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RayneAuthor Commented:
Well yes, I need to somehow loop thorugh each slicer option so that the slicer cache is updated  - as that stores the selection value in the page footer. So when I open the workbook, something needs to happen so that the slicer cache is updated with those values so when I print I see the footer already refreshed
0
 
Steven HarrisPresidentCommented:
I think I know what you are after now:

ActiveWorkbook.SlicerCaches("Slicer_Rep").ClearAllFilters
ActiveWorkbook.SlicerCaches("Slicer_Rep").PivotTables(1).PivotCache.Refresh

Open in new window


You will want to add this to a Workbook Open event.
0
 
RayneAuthor Commented:
thanks Sire
0
 
Steven HarrisPresidentCommented:
Not a problem!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now