Unique passwords for Drop Down Menus

[Firazed]

New poster here. I found a solution from this website for my problem, but I have yet to implement. There are a few additional factors for my problem so I would like to design knowing whether it will work as intended. Keep in mind I'm 100% new to VBA, macros. So I may have other problems.

Here's my problem: I want users to be able to view only their data, based off of a drop down menu. They each need to have their own password. BUT, there are a few users who are able to see ALL data.

This is the "Macro" solution posted by another user in a different thread for reference:

Please Login or Register  to view this content.

I want users that have privilege to see all stores to still have the option to view stores individually, so it won't suffice to simply include an "All Stores" selection in my drop down menu.
If I provide these users with a password to unlock the sheet entirely from the Review tab, will it remove the prompt for passwords in the drop down menu?

I suppose it's possible to set up two passwords for each store, one a master password, but I don't want the end user to be prompted with a password each time they want to switch their store view.

All ideas appreciated.

[dflak]

I'm going to take a guess as to how the workbook might actually look since it is not posted. I'll assume that there is some source data that contains information as to what information belongs to Mike, Pete and Alan.

What I would do is put this on a very hidden sheet, filter it according to name and then copy the filtered data to a sheet where they can use it, perhaps even as source data for a pivot table.

For the users who get to see all the data, instead of passing a name as the filter criteria, pass "*" - this should filter in all records.

[Firazed]

The code was for a spreadsheet that was only related to my problem.
Currently, I am using a feature that allows me to select a store from a dropdown menu, and all cells will populate with the totals for that specific store only. The problem is that the user can just change the dropdown menu. Even if I protect the sheet, if they type in any other store, they can see the data until they click OK on the error box!
My work around solution is to print the sheet to PDF and send each individual store their totals only.
There are two reports- individual and stores. I use filters as you mentioned for individual totals, but store totals are isolated using the dropdown feature.
My goal is to send ONE email, to all store managers and senior management. They would then be prompted for their password, and only have access to what they have privilege to see.

[dflak]

Assume that the data is on Sheet X. Assume also that each store is associated with a particular manager.

On Sheet Y you have an Excel Table which is emptied on file open and closed or you ship it out empty. Build a pivot table based on this sheet. Obviously it will be empty too.

When a manager validates credentials set a variable to the manager's name. Then use this variable to filter the data on Sheet X.

Copy the filtered data to the table on Sheet Y. Now the pivot table will have access only to stores associated with that manager.

So when a manager opens up and enters credentials, only the data on Sheet Y is available. Sheet X is very hidden and its existence isn't even known about. In fact, you can put in code to delete Sheet X once the credentials are successfully verified.

Now for people who are allowed to see all stores, set the variable to "*" instead of a specific name. This will filter in all records. You'll probably need a table that links login, password and name passed to the variable.

Even with a very hidden Sheet X, there are security concerns. The casual person probably could not get to the data. But as with any system, if the data are there, they can be gotten to. If this is acceptable, then you can stop here.

A more secure way would be to set up the spreadsheets as I described. Then loop through the manager names, make a copy of the file including a refreshed spreadsheet, and eliminate Sheets X & Y on the copy. Save the copy with the manager's name and then mail it out (even this part could be automated if you also include the email address in the table with the credentials). This way every manager gets a usable pivot table and only has access to the appropriate stores because the other data do not exist in the copy of the file the manager gets.