VBA code to lock a slicer or make it not visible?

[codeliftsleep]

Here is the dilemma I am running into.

I am designing a report that will be viewable by both managers and employees.

I have slicers attached to Employee, Level 1 Manager and Level 2 Manager fields that control all the pivot tables.

The issue is, once I run the code upon opening the workbook to determine who the user is and to set the slicers appropriately, I want to either:

A) Lock them CONDITIONALLY(which is where the problem is) or
B) Make them not visible.

For instance:

• A Level 2 manager should be able to make changes to the Level 1 manager and Employe slicer so they can look at people that report to them however they want.
• A level 1 manager should only be able to make changes to the Employee slicer to see the people that report to them individually.
• An employee should not be able to make ANY chanegs to the slicers, as they are only allowed to see their data.

However, I cannot find anything in code that will either allow me to lock the slicers or make them hidden. This is an issue because while I can always just lock the Manager 2 slicer once its set by protecting the sheet, I can't lock the level 1 or employee slicer because some people need to be able to change them if they want.

Is there a way to do this via VBA? I am using Excel 2013

 

[veryamusing]

The slicer object has a property for locked, but that's if the Worksheet is protected:

[FONT=&quot]True[/FONT][FONT=&quot] if the slicer is locked when the sheet is protected. [/FONT][FONT=&quot]False[/FONT][FONT=&quot] if the slicer can be modified when the sheet is protected. The default value is [/FONT][FONT=&quot]True[/FONT][FONT=&quot] .[/FONT]

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/slicer-object-excel

Try adding and deleting them depending on the user? Or maybe on workbook open display an input box for the user to enter a code? Then add / delete depending on the value typed into the box...

I'll keep thinking and let you know if I think of something more elegant.

 

[codeliftsleep]

Initially I got it working by setting the height property to 0 but this jacked up some of the slicers and they "disappeared" until I closed and reopened it and then they were out of place when I reset the height, so that wasn't a viable option.

I actually found the solution by recording a macro when I was looking at the selection pane and using the hide/show buttons

To hide/show all of the slicers on a page

for i = 0 to ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Visible = msoFalse(or msoTrue to show)
next i

to do individual ones:

ActiveSheet.Shapes.Range(Array("Name")).Visible = msoFalse(msoTrue to show)