How to allow editing of a range in a protected worksheet with VBA. If we protect a worksheet all the cells are locked and cannot be edited without a password or unprotecting the worksheet. However, we can lock specific cells or ranges before protecting the worksheet with a password. Also, there is an option available to allow specific users to edit locked ranges.
The following steps need to be followed to protect or lock specific ranges in a worksheet:
(1) Select the relevant worksheet
(2) Click the ‘Review’ tab
(3) Click ‘Allow Users to Edit Ranges’. This option is active only if the worksheet in question has not been protected
(4) In the window that pops up select ‘New…’
(5) Under the ‘Title’ heading enter a range name like ‘myeditablerange’
(6) Under the heading ‘Refers to cells’ type ‘=’ followed by the range of cells, for example, ‘=D2:F4’. Of course, you can click on the arrow like Collapse Dialog button, select the range and again click on the button to have the range automatically filled.
(7) Under the ‘Range Password’ option enter a password which will open the range for editing later.
(8) You’ll be asked to confirm the password. Enter it again and click OK
(9) Now click on ‘Protect Sheet’, enter a password and finally reconfirm the password.
Now the defined range is locked against editing and can be unlocked for editing only after supplying the password without unprotecting the sheet.
Watch the video below:
Watch this video on YouTube.
Here’s the useful VBA code taken from the recording:
‘ AllowEditableRange Macro
ActiveSheet.Protection.AllowEditRanges.Add Title:=”Editable”, Range:=Range( _
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True