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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.