I think best is to copy the sheets for each person to a different file.

You can also hide the four for person nr. 1, but you also have to make a copy of the file, unhide the four and then hide the other four for person nr. 2.

So that´s even more work.

Don´t know of a system where you can ´automate´ this hiding.

Frans

Hello Dritan,

Personnally I would do as Frans suggests, giving each person a copy of the file containing only their sheet. I would then use Power Query to gather the data from the different files and so forth.

But to answer your question. There are some methods you can use, check out the blog articles for two different approaches.

https://www.myonlinetraininghub.com/interactive-excel-web-app-dashboard

https://www.myonlinetraininghub.com/hide-delete-and-sort-sheets-with-vba-userform

Br,

Anders

Hi Anders,

Thank you for your answer,

I would need that the excel file to pop-up with a windows form that required to fill Username and a password on the form, after the Username and the password is enter than excel open the sheet for a correspondent user.

Hi Dritan

What you asked for is quite complicated and not fool-proof.

I am assuming you know a bit of VBA as you will need to maintain the file yourself.

The username, password and worksheet name is stored in the Setup sheet which is hidden.

Modify to suit your needs.

Good luck.

Sunny

Hi SunnyKow,

Great work 🙂

Here is my need

I would like when I open the file and when i pres the button "Login" I want a small window pops-up. In the windows on the left side I would like to have an drop down box, when I can select the list of the user, than in the right side Password.

The other things that I need, is that possible to add users, and sheet?

I'm not finding the password?

Could I have the VBA code about this file?

best regards,

Dritan

Hi Sunny

how to not open sheet, bcoz already hide sheet i have opened i want to protect user1 and other also

Thanks/Saliha

@Dritan

The codes are not hidden. You can view them by pressing ALT+F11.

The username, password and worksheet name is stored in the Setup sheet which is hidden.

You can add users, assign passwords and new sheets if needed. 

You may want to make the sheets very hidden so that casual users will not he able to unhide the sheet manually.

This option is available in the VBA codes. You will also need to password protect the VBA codes.

Like I said earlier, if you wanted a VBA solution you will need to know some VBA. Otherwise you will have difficulty maintaining the file.

@Saliha

I don't understand what you wanted. If you wanted to hide the sheet that was already open (eg User1) , you only need to select the Main sheet.

This will automatically hide all other sheets.

Sunny

I have modified the file and added Admin as one of the user. The password is Admin. You can change it later if you wish.

Only the Admin can see the Setup sheet where you can add/remove users and assign/change their password and sheet access etc.

There is also a button to unhide all sheets for you to maintain them. You can then auto hide them by activating/selecting the Main sheet.

All sheets (other than Main) have been set to very hidden so that users cannot see and unhide them (unless they use VBA).

You will still need to protect the VBA project to prevent users from accessing the codes. It is currently unprotected.

Please do not rename the Main sheet.

Sunny

Hi Sunny,

Awesome work :), thank you for your contribution.

Here I have some basic question related with VBA which I know a bit.

Can you provide me to protect the VBA project to prevent users from accessing the codes?

I put it in screenshot on a word document about some code that you put it to the file.

Thank you so much for your support,

Dritan

Hi Dritan

Protecting your Project

• Press ALT-F11
• Select Tools-VBAProject Properties…
• Select the Protection tab
• Check Lock project from viewing
• Enter your Password and Confirm password
• Click OK

Answering your questions:

• Option Explicit – You need to declare all your variables before you can use it else it will flag as an error.
• That code will hide all worksheets other than Main when the file is opened. Just in case someone saved the file with their worksheet visible.
• Password – password to open the file
• WritePassword - password to modify the file
• That code will unhide all worksheets.

Password & WritePassword above can be set when you save the file with File – Save As – Tools – General Options

Hope this is clear enough.

Sunny

 i got the answer from your above message and thanks

Thank you Sunny,

Super 🙂

Hi Dritan/Saleha

No problem 

Glad to know it is helpful.

Sunny

Thank you once again.

What if I want to make it this file available in Google.doc? So the users can enter and edit their restricted sheet form distance?

This will be very useful.

Best Regards,

Dritan