Log In
Register
Home
July 19, 2014
Offline
Hi to all,
How can I make an Excel sheet visible to a user, but invisible to the other one.
So if I have a excel file with 5 sheets, I want in this file to work 5 other users but each of the user can see only one page not all the page?
How can I restricted this?
Any help please?
Best Regards,
Dritan
VIP
April 21, 2015
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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.myonlinetraininghu.....-dashboard
https://www.myonlinetraininghu.....a-userform
Br,
Anders
July 19, 2014
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
July 19, 2014
Offline
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
December 8, 2017
Offline
Hi Sunny
how to not open sheet, bcoz already hide sheet i have opened i want to protect user1 and other also
Thanks/Saliha
VIP
Trusted Members
June 25, 2016
Offline
@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
VIP
Trusted Members
June 25, 2016
Offline
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
July 19, 2014
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
December 8, 2017
Offline
i got the answer from your above message and thanks
July 19, 2014
Offline
Thank you Sunny,
Super 🙂
VIP
Trusted Members
June 25, 2016
Offline
Hi Dritan/Saleha
No problem 
Glad to know it is helpful.
Sunny
July 19, 2014
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
Hi Dritan
Are you referring to Google Sheets? I have very little experience using Google Sheets.
It doesn't support VBA but uses GScript instead. This it self is already a big disadvantage for me.
Furthermore the number of cells is limited to 2 million per workbook (irrespective of the number of worksheets) which is just not enough for my usage.
Its charting is, at best, very basic for me. I still prefer Excel at this point of time.
Sorry I can't help on this matter.
Sunny
July 19, 2014
Offline
Hi Sunny,
I understand, but what about other alternative from Excel, like from excel 365, or from One-drive Excel?
Is there any of above options to support such a file?
Thx,
Dritani
VIP
Trusted Members
June 25, 2016
Offline
Unfortunately I am not familiar with them. Maybe others could assist you on these.
October 5, 2010
Offline
Hi Dritan,
Excel online doesn't support VBA so you'd have to look for another way. Perhaps if you look at this post it will give you some ideas of how to embed a workbook and restrict user access.
https://www.myonlinetraininghu.....-dashboard
Regards
Phil
1 Guest(s)
