Try this and reply if still having problems. Is there any chance I could get the password for the Main sheet? The default value is True. John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. But in Excel, you need to protect sheets one by one with its built-in function. I am using the copied and pasted formulas from above but receive Run-Time error '9' subscript out of range.
This could occur if you cancel out of a workbook close operation or if another macro deletes that property. I know its a cheat way of doing it, but that's what I can think of for now. If you applied conditional formatting before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition. However, here's the method I use. This article was co-authored by our trained team of editors and researchers who validated it for accuracy and comprehensiveness. This refers to the composition of the worksheets within the Excel file. The code I have provided should be used as a starting point for people to develop their own variants to meet their particular requirements.
If a non-admin user opens the file and makes a change and then saves the file, when I save the file I get a message saying a user has made some changes. Step 1: Input Username or name of Worksheet - Name of Student. You can share the workbook and provide the password only to the intended users. AllowInsertingColumns Optional True allows the user to insert columns on the protected worksheet. It could be possible to create an array and for supporting code to deal with, but I suggest you use the code given on 16th March 2016, it is structured in a different way using a hidden worksheet to hold the list of Users, their passwords and the sheets they are permitted to view. Because Microsoft Excel uses a very simple encryption algorithm for worksheet protection. I went through all the conversations to avoid repetitive questions.
There are non-traditional ways you can use, as well. This worksheet is normally hidden but is visible when a user with Admin rights opens the workbook. Type password twice for these sheets we select just now. For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password for it. Now on to actually adding a password to an Excel file. Zane Usernames, passwords and permitted sheets to viewable are not in the code but are all contained on a worksheet called Users.
Thank you in advance for your help. Click Ok, and another dialog pops out for you to entering and retyping the password, see screenshot: 4. Cells Val m , 3. Any hints or tips for this? As I said, the above represents the traditional way to approach the problem. I have been testing this with thoughts of implementing something similar. Omar I am pleased you find the workbook useful. Scenarios Optional True to protect scenarios.
I would strongly advise also protecting the structure of the workbookso that users cannot delete sheets, for instance. Understand the conditions under which you can do this. Thanks for all the help! AllowInsertingRows Optional True allows the user to insert rows on the protected worksheet. Having two different views available simultaneously? These open the user form when the workbook is opened, and protect the worksheet when the workbook is closed. Your worksheet is now protected. Step 4: In General Options dialog, enter password to modify.
This is though way beyond the scope of this tip, and is going to be a bespoke solution requiring quite a bit of work to implement. This may not solve your problem though as the macros that check the password is correct are looking for a specifically named text box. Each sheet should correspond to a different username and password. You quite rightly hide all the sheets before the save, but you must only unhide the sheets after the save that a User has the privilege to view. For a chart, this protects the entire chart. Redirecting Of Banned Users provided by - Copyright © 2019 DragonByte Technologies Ltd. Where the file is stored will not affect its level of protection.
At this point, you have two layers of protection: one at the sheet level and one at the workbook level. Password protection provides the workbook owner you more control over who can make changes to the structure of your workbook. If they supply the correct password this is the one that you specified in steps 17 and 19 , then they can edit anything they want in columns E and J. Value End With If admin Then For Each Sh In ThisWorkbook. If the worksheet was not protected with password, clicking the Unprotect Sheet button is all it takes to unlock the sheet.
Otherwise, explain how you want this 'results sheet' approach to work! Click File — Info on workbook. Did you find this post helpful? Only the worksheet permitted to be viewed by particular user are ever visible. But certain ActiveX controls are not supported, and more obviously operating systems calls do not work. Note that I have copied your code as is but dropped in an existing workbook into your template. Works like a charm now.