Excel VBA - Restrict one or multiple worksheets / slicers for users using password 2

[arvarr]

Hi
Hoping someone can help.

What I'm trying to achieve is:
1. I have a workbook with multiple worksheets. There are 5 or more users. What i am trying to achieve is to restrict each user to look at certain worksheets. It can either be 1 or multiple worksheets.
2. Within the same workbook, there is a Master data with slicers from the pivot table. Is it possible to restrict what the users can select from the slicers / filter from the pivot table?

Thanks,
arv

 

[combo]

(1) is simple, create 'Intro' sheet, hide with xlVeryHidden worksheets that should be hidden for any of users. In Workbook_Open test user name (Application.UserName or Environ("USERNAME"), note that in general they return different values), unhide necessary sheets; hide again before save. You should password protect VBA project and workbook structure, but this protection is very weak.

In case of (2), probably the easiest way to achieve this would designing a worksheet or userform with user dependent UI and show output generated by code in protected sheet. IMHO excel is not a good tool to handle user rights at this level.
Optionally, if a set of filter entries is known, a intermediate table with rights can be created, that combined with raw data and user name in Power Query, with pivot table referencing the query. This would force user to work with permitted data only. Again security (access to queries) is the issue.

combo

 

[arvarr]

Hi combo
1. Can you provide a sample? What is the purpose of the Intro sheet?
2. Sounds very complicated.

Thanks,
arv

 

[SkipVought]

Picture this.
Whenever the workbook is opened by anyone, they only see ONE SHEET. Every other sheet is VeryHidden.
That ONE SHEET only has "ENABLE MACROS".
If macros are not enabled, there is no place to go and nothing to do in the workbook.

If the user enables macros, the macros determine if this user is "on the list"
If not, the workbook just saves/closes in the INITIAL configuration.
Otherwise, according to the "rights" that THIS user has, the workbook/worksheets is/are configured for her/his use.
When they are done, the workbook just saves/closes in the INITIAL configuration.

It's not a simple task. Excel is designed to be wide open, allowing the user to get their hands dirty way down in the data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

Skip perfectly described the logic. Mind that Workbook structure should be protected, otherwise a code in other workbook can be used to change visibility of hidden sheets. Anyway, excel workbook is a compressed tree with xml documents, no problem to edit them, so the real protection is close to nothing.

The idea behind (2) is simpler, you do the same with visibility of sheets, but instead of blocking filters and pivot tables, one or more queries prepare data for specific user in common or specific pivot table(s) or table(s). The same sheets can be exposed to all users if output data structure is the same. Some experience in Power Query is necessary.

combo

 

[arvarr]

Hi combo and skipvought
Much appreciated with your help.

I have managed to sort out issue 1.

Thanks heaps!

Regards,
arv