December 26, 2018

Allow Editing in Protected Worksheet with VBA

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:

Sub AllowEditableRange()

‘ AllowEditableRange Macro


Range(“D3:W27″).Select
ActiveSheet.Protection.AllowEditRanges.Add Title:=”Editable”, Range:=Range( _
“D3:W27″), Password:=”123”
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Further reading:

Lock or unlock specific areas of a protected worksheet

2 thoughts on “Allow Editing in Protected Worksheet with VBA

  1. Dear Sir,

    I have a query which required your attention.

    I have a data dump in excel sheet with number of columns, say Column A to Column AA. I have to give this data to different bankers in their own pre-defined format, but all the required fields are there in data dump. Based on the criteria i.e; “name of the banker” from data dump, i have to copy required columns from dump and paste in the bankers format.

    Please help me out on this.

    Thanks in advance.

  2. Dear Sir,

    I’ve an Excel macro file. I’ve made the sheet table. Only cells (A14:AC71) named Range as “Read_Only”. This name only for references and only admin can make the changes.

    From row A1 to A13 – can make changes. If we need can insert row as well between A13 and A14. In that case the “Read_Only” range will move downwards.

    I able to create “Add row” (with copy the above row with formats & formula but with out values)

    Challenges:
    Keep the specific range as Read Only and admin can do the editing if so with password.
    Other rows free to do the changes.

    Please help me if you have Sir.

    Thank you.

Comments are closed.