Allow users to edit ranges in Excel
One simple command that elevates Excel Sheet protection to a whole new level. Imagine wanting to protect a Worksheet in Excel but with a few requirements.
- all coworkers can view but they must not be allowed to edit the Worksheet
- four special coworkers (John, Paul, George and Richard) all have their ranges that they and they alone can edit
With the Protect Sheet command, you could only either unlock cells or leave them locked.
The first of our two conditions above already means that you have to lock them, to avoid that, maybe one could use the general options while saving, set the Password to modify and only give the fabulous four that password.
That would prolong the opening of the file in Excel, but still, the second condition above would not be met and could not be met only by using the Protect Sheet, Protect Workbook and setting Password to Open or Password to modify. That’s where Allow users to edit ranges comes in.
In Excel we select the range of cells, that we wish to let someone format, and select REVIEW/Allow users to edit ranges and you will get to the Allow users to edit ranges dialog box
In the Allow users to edit ranges dialog box you select New, give this range a title, the Refers to Cells: will be filled out automatically by the initial selection and the most important thing, you must set a Range Password for this Range. In a classical domain environment you can also use the Permissions button and automatically let Users or Groups from the domain to edit this Range.
After pressing OK you will have to retype your password and the Range is set. You can change this settings at any time by selecting the Modify button.
You must do this for all the desired ranges.
It’s also worth mentioning that we left all the cells locked! After this set up, if you want for this to take effect, you must protect the Worksheet. Until you do, all this settings have no effect. But after you either right click on the sheet name and select Protect Sheet or find the same command on the REVIEW tab set the password (which has no connection to the previously set passwords), and allow users to select the locked cells(!).
So now John opens this workbook, all he has to do is to either double click into his range or start typing into that range. He will get the Unlock Range dialog box where he inputs the password that was given to him.
After he does so, he can edit that range in any way he desires.
All the other cells and Ranges remain locked! So as promised, this takes the Sheet protection to a whole new level!
Comments 10
Leave a Reply
Excel Unplugged acknowledgements
Donate
@Gasper, Good One! I liked your presentation. Awesome!
I do have a similar one on Formula Editing tips in my blog, http://www.exceltoxl.com
http://bit.ly/1zTyV0N
This is good, but after having unlocked the range (by entering the password), the range then remains unlocked when the sheet is saved, meaning anyone can thereafter edit that range. The only way I could find around this was to unprotect the whole sheet and protect it again, which defies the point of having separate passwords for each person’s range in the first place. Is there any way to make the range protected again when the sheet is subsequently saved to continue the functionality for further use?
The only thing you shouldn’t do is unprotect the sheet… As long as the Sheet remains protected, the Range remains protected by the given password. And you do not need to unprotect the Sheet, to insert the Password given to the range and unprotect that.
I think we may be talking at cross purposes. I have found, that after setting up a user edited range as you have described in the post, if you enter the password to be able to edit the user range and then save the sheet, when you open it again the range is not protected, although the remainder of the sheet remains protected. Therefore the range password only has to be entered once and the range thereafter is permanently unprotected. I want to be able to ensure that each time someone opens the sheet, they have to enter the password to be able to edit the range. At the moment that doesn’t seem to work.
Ok, my bad, then what separates you from eternal happiness is that those cells have to be locked! So on the Protection Tab of the Format Cells Dialog Box you have to leave them locked! Then it will work exactly like you want. But you have to let the locked cells to be selected when protecting the Sheet.
Thanks, not sure why it didn’t work for me before but seems to be now!
Cheers!
Philip,
Did you ever find a solution to this? I’m having the same problem.
@Jamie…
did you read the entire comment section?
Can you still share the workbook whilst having the protection on the ranges? I want to share with multiple users for them to add data into their specified ranges but not mine but I also have the workbook open at all times, whereas they will only open and input when needed.
So there are two “sharing” options available now. The classic “Review/Protect and Share Workbook” (and that would work) and the OneDrive, OneDrive for Business or SharePoint sharing which I would have to test for this, but I’m guessing should work.