2 Levels of Security

[slaterm4]

In my company I have to divide up our expense reports by Cost Center. It would be easier to just send everything out in individual workbooks but the higher executives do not want to sort through 10 different ones. Is there a way I can create a login and password that directs you to what you are allowed to look at. We have 10 different cost centers and I need to create one for each of those groups that only allows them to view only their page. Then there is the executives and presidents that need to be able to view the entire workbook. I created one where people could put a password in at each page but it displayed the information on the page in the background. Is there something else that I could use with a step by step process on how to create the two different levels of access.

Please Login or Register  to view this content.

This is what I used previously but like I said this information displayed when you clicked on each page is not going to work. Also the executives do not want to type in a password at each page.


Thank you in advance for your help

[Winon]

Hello slaterm4,

Could you please post a sample Workbook, explaining exactly what you require where, and we will see how best to assist you?

Regards

[slaterm4]

It is a workbook with 10 different tabs one for each of our cost centers. I need to create a login that will take each of those 10 groups to their designated tab. I also need a login or someway to have the executives be able to access the entire workbook without having to type each cost center's password in. It does not even have to be a login just a way so each cost center cannot look at the other groups and the executives can look at all. I cannot post my companies confidential information on here. Is there even a way to do this?

[shg]

How bad is it if a non-authorized person can access any sheet in the workbook? If the answer is 'very bad', Excel is not the tool for you.

[slaterm4]

No it's not bad at all. I would just like to know if I can build something like I asked above to give access at different levels and if I can build a macros for that. The reason I am not posting an example of my companies reports on the internet is they are for company personel's eyes only. These reports are only seen by the manger of each cost center. I would send it out to all but am trying to prevent a person from going into a other tab and viewing. If our employees are able to disable and delete the macros we can figure something else out but at this time we would like to attempt this. Two levels one to view the entire workbook then 10 sepeparte entries to view one tab at a time. Security and other issues aside is there a macro to do this so we can build it and try it out and see if our executives are satisfied?

[Winon]

Hello slaterm4,

I echo what shg's concerns are. If anyone would open your Excel Workbook in Open Office, Front Desk, or some other compatible platforms, they would be able to see what they want regardless of whatever "Securty" measures you may have in place with Excel.

Regards

[ag273n]

this is not the best but i recommend:

first add a macro that hides all other sheets except a dummy sheet (lets call it sheet1 - an empty sheet good as nothing)
-so all other sheets are set to "very hidden" (the sheet will NOT show by simply right clicking the tabs to unhide)
second you can create a user form where they can input their password and add your code...
third - if their password is wrong - then it will not unhide or show the sheet...
fourth - set the vba project property to hidden from view with a password...


the difference of this from your main solution is that - the sheet is already hidden from view before they run the macro, running the macro will unhide it if they have the right password,
now if the sheet is already visible before they run the macro, then simply disabling the macro will grant them access to any sheet...

ofcourse, if they have some other tools to unlock the vba project property, then thats the only time they can have access to all other sheets...

[slaterm4]

I understand there are security threats. As we send it out right now everybody is able to view everyone's. I am thankful for everyone's help. But all I really need to know is if I can build a macro and if so what would it be. I am experienced with Excel and understand it is not the best option to go to something like this. My weakness is macros and even though I'm learning I am not great with them. Any help on the macros portion not the security would be helpful.

[Pete_UK]

Just to add to that, your macro would need to hide that sheet and reveal the dummy sheet when the file is closed, so that you are at the same starting point next time your user opens it.

Pete

[ag273n]

this code can hide all other sheets except a sheet named "Sheet1"

Please Login or Register  to view this content.

you can add that code to the workbook_beforeclose sub in your workbook and it should work... HOWEVER, you need to search first and find out how to unhide the sheets manually through the vba editor so you can get the sheets back... the sheets hidden by that code cannot be easily unhidden by just clicking unhide in the worksheet tabs...

[ag273n]

just to add...

the method i mentioned should keep anyone from seeing the sheets intentionally hidden even if they use another platform that can run excel... only if they use a special cracking program to get the vba project password can they unhide the sheets..

so to a view of an average person without a password, the workbook will apear only with a single empty worksheet regardless if they disable the macro or enable it...

[Pete_UK]

The technique is described in detail on Chip Pearson's site here:

http://www.cpearson.com/excel/EnableMacros.aspx

though he uses it as a way of forcing the user to enable macros.

Hope this helps.

Pete