Thank you so much Nicos for this guide. I have spent so much time trying to work out how to do this watching very complicated methods - your method and explanation was so simple
I have a worksheet that has a data validation that selects from (and vlookups to) the "hidden cells" on the same worksheet. When I protect the sheet, I get an error when I select one of the drop downs. I guess this error arises because the data validation is list that is mapping to the protected hidden sheet? If I place the data in a tab and hide it, it works ....but is there a way to have it on the same worksheet without encountering an error upon selection?
Nevermind. I figured it out. I needed to first Unlock the cells that were "data validations" by right click>format cells>protection>uncheck locked .....before hiding and protecting my sheet
I am a bit confused as i am finding it hard to teack what you are trying to do. In general however, make sure your validation is not protected so that its value can change. Hidding a sheet and protecting the book will not create issues. If you want to protect inly portions of the sheet, this is possible and explained in the tutorial. Not sure if my general answer helped.
Again I want the user to access certain tab and hidden others. I want to hide specific tabsheet with password to prevent curious user and messes up the hidden sheet. The hidden sheet is fed with information based on what the user input on the questionnaire form. What if I have v or xlookup behind the scenes will stop these functions working?
Great video! Is there any backend benefit/enhanced security by preventing any editing for the entire workbook upon initial entry (file -> passwords -> prevent editing = making it read-only) vs protecting/"locking" all sheets/ workbook from within the file itself (Review Tab -> Protect Workbook/Protect Sheet)? Other than differences in read-only versus locking from a frontend accessibility standpoint (still can unhide arrays in read-only for example), is it more advantageous to use both forms/"layering" of security, or will the locking-features from within a file be enough?
File password is more secure. Protecting the workbook or spread sheet with passwords can be reversed. I have tutorial on hacking the protectiin passwords. The file password is a much more secure. Tutorial here: th-cam.com/video/UIysPdpo-X8/w-d-xo.htmlsi=aBcSLUaTzBnkOAak
Hello, Maybe I missed it, but I want to hide a tab, have it password protect it, but want the user to freely update the shown tabs. Protect Workbook won't do this , and neither will Protect Worksheet. Is there a simple way to do this with no coding/macros? Thanks.
hide the work sheet you want to hide. Then lock the workbook with a password. This will not allow them to unhide the hidden sheet but they can work on the other ones.
I have a project tabQuestionnaire, tabAnswers(hidden) where the person responds a questionnaire when clicking to select answers, it input the answers and calculate behind the scenes on another sheet that is hidden. My question is when the person responds the questionnaire, and if I protect with password following your method, will the hidden sheetAnswers be able to receive/update the answers from the questionnaire? The answers are placed automatically on the tabAnswers by some VBA few codes.
The fact that it is hidden will not stop its operation. If you have not locked the cells and they are able to be updated in the hidden sheet, then it will work fine. The best way to find out is to test it and make sure it works the way you want.
If I have 3 sheets in Excel file, can I manage like "admin/ manager can see all 3sheets, lead can see 2nd sheet, and remaining team can see only 3rd sheet".
Thanks so much for showing how to hide worksheets. However is there a way I can encrypt the entire workbook with a password before entering it, but keep it on auto save so multiple people can still edit at the same time?
Not sure on what you are asking. I think you want to lock particular cells so that other users can not edit specific cells but are able to modify other cells in a spreadsheet. Yes? If this is what you want i can reply with instructions
Thank you so much Nicos for this guide. I have spent so much time trying to work out how to do this watching very complicated methods - your method and explanation was so simple
Thank you for the feedback. That is fantastic. I am glad you found it useful
I have a worksheet that has a data validation that selects from (and vlookups to) the "hidden cells" on the same worksheet. When I protect the sheet, I get an error when I select one of the drop downs. I guess this error arises because the data validation is list that is mapping to the protected hidden sheet? If I place the data in a tab and hide it, it works ....but is there a way to have it on the same worksheet without encountering an error upon selection?
Nevermind. I figured it out. I needed to first Unlock the cells that were "data validations" by right click>format cells>protection>uncheck locked .....before hiding and protecting my sheet
I am a bit confused as i am finding it hard to teack what you are trying to do. In general however, make sure your validation is not protected so that its value can change. Hidding a sheet and protecting the book will not create issues. If you want to protect inly portions of the sheet, this is possible and explained in the tutorial. Not sure if my general answer helped.
Again I want the user to access certain tab and hidden others. I want to hide specific tabsheet with password to prevent curious user and messes up the hidden sheet. The hidden sheet is fed with information based on what the user input on the questionnaire form. What if I have v or xlookup behind the scenes will stop these functions working?
thanks, it really helped
I am glad. Thanks for your comment.
Great video! Is there any backend benefit/enhanced security by preventing any editing for the entire workbook upon initial entry (file -> passwords -> prevent editing = making it read-only) vs protecting/"locking" all sheets/ workbook from within the file itself (Review Tab -> Protect Workbook/Protect Sheet)?
Other than differences in read-only versus locking from a frontend accessibility standpoint (still can unhide arrays in read-only for example), is it more advantageous to use both forms/"layering" of security, or will the locking-features from within a file be enough?
File password is more secure. Protecting the workbook or spread sheet with passwords can be reversed. I have tutorial on hacking the protectiin passwords. The file password is a much more secure.
Tutorial here:
th-cam.com/video/UIysPdpo-X8/w-d-xo.htmlsi=aBcSLUaTzBnkOAak
Hello,
Maybe I missed it, but I want to hide a tab, have it password protect it, but want the user to freely update the shown tabs. Protect Workbook won't do this , and neither will Protect Worksheet.
Is there a simple way to do this with no coding/macros? Thanks.
hide the work sheet you want to hide.
Then lock the workbook with a password. This will not allow them to unhide the hidden sheet but they can work on the other ones.
@@paphitisn OK. Lock the workbook. I will try that. Thanks.
I have a project tabQuestionnaire, tabAnswers(hidden) where the person responds a questionnaire when clicking to select answers, it input the answers and calculate behind the scenes on another sheet that is hidden. My question is when the person responds the questionnaire, and if I protect with password following your method, will the hidden sheetAnswers be able to receive/update the answers from the questionnaire? The answers are placed automatically on the tabAnswers by some VBA few codes.
The fact that it is hidden will not stop its operation. If you have not locked the cells and they are able to be updated in the hidden sheet, then it will work fine.
The best way to find out is to test it and make sure it works the way you want.
thank you
If I have 3 sheets in Excel file, can I manage like "admin/ manager can see all 3sheets, lead can see 2nd sheet, and remaining team can see only 3rd sheet".
not sure what your question is. These settings can be implemented on any Excel workbook, it makes no difference if the file is saved in OneDrive.
@@paphitisn plz see the edited question above
@@ramkumaralajingi3239 I understand what you mean - that is a very good question.
Thanks so much for showing how to hide worksheets. However is there a way I can encrypt the entire workbook with a password before entering it, but keep it on auto save so multiple people can still edit at the same time?
Sure. Open the workbook, go to File --> Info --> Protect Workbook--> Encrypt with password
how can i protect sheet with password depending on selection in cell in main sheet?
Not sure on what you are asking. I think you want to lock particular cells so that other users can not edit specific cells but are able to modify other cells in a spreadsheet. Yes?
If this is what you want i can reply with instructions