Excel/Unprotecting dynamic rows in a protected worksheet
QUESTION: Hi Damon,
Need your help in doing the below task using Formula/Macro.
I would like to unprotect the dynamic rows where it contains the word "Production Plan (Units) - Revised" in column B.
As per this example I would like to unprotect the cells C7:H7, C10:H10, C15:H15. I want to unprotect the whole row after the cell contains particular text (i.e. "Production Plan (Units) - Revised" in this case).
Please note that range will be dynamic. Please let me know if you have any question.
ANSWER: Hi Prabhat,
Sorry, but I need a bit more information before answering your question.
You mention that the range will be dynamic. Do you mean by this that
1. you are using a Dynamic Named Range to specify the entire range that is to be searched?
2. the worksheet is dynamic in that data are changing dynamically?
If #2, then are the changes due to
a. manual editing?
b. pasting of data from some other sheet or location?
c. connection of some of the cells to dynamic data feeds?
e. a combination of these?
Also, I assume that column B is not locked, only column C:H. Is this correct?
In addition I assume that the worksheet is protected, and that if a cell in column B that previously contained "Production Plan (Units) - Revised" is dynamically changed such that it no longer contains "Production Plan (Units) - Revised" the code should lock (protect) it. In other words, you want the code to both unprotect and protect rows depending on whether they contain "Production Plan (Units) - Revised".
---------- FOLLOW-UP ----------
QUESTION: Hi Damon,
Please note the below points -
1. Sheet is not protected and data is changing dynamically.
2. Data in this sheet is referenced with a pivot table in another sheet.
3. I want my users to be able to edit/change the data only in the field (Production Plan (Hrs) - Revised), it can be C:H or C:J depends on the data, as it keeps changing. and they can change in any way by pasting or manually editing the cell.
4. Remaining cells in the data should be protected.
Please let me know if you need more clarification.
Hi again Prabhat,
Okay, here is some code that I believe does what you want:
Private Sub Worksheet_Change(ByVal Target As Range)
'check whether changed cell includes column B
Dim TRow As Long
TRow = Target.Row
If Union(Target, Cells(TRow, "B")) Is Nothing Then Exit Sub
If Cells(TRow, "B") Like "*Production Plan (Units) - Revised*" Then
Range(Cells(TRow, "C"), Cells(TRow, "J")).Locked = False
Range(Cells(TRow, "C"), Cells(TRow, "J")).Locked = True
'Checks column B of all rows and locks if they contain "Production Plan (Units) - Revised"
Dim iRow As Long
Dim LastRow As Long
LastRow = Cells(65536, "B").End(xlUp).Row
Range(Cells(2, "C"), Cells(LastRow, "J")).Locked = True
For iRow = 2 To LastRow
If Cells(iRow, "B") Like "*Production Plan (Units) - Revised*" Then
Range(Cells(iRow, "C"), Cells(iRow, "J")).Locked = False
'uncomment this if you want this procedure to protect the sheet
You should replace "password" in the code to a real password of your choice. This is the sheet protection password that is necessary to protect the data from C:J in the rows NOT containing "Production Plan (Hrs) - Revised" in column B.
This code should be installed in the Sheet1 code pane. To do this, right-click on the tab for Sheet1 and select View Code. Then paste this code into the Code Pane.
Next, go back to Excel and run the Sheet1.CheckAllRows macro.
Now you should find that only the rows containing "Production Plan (Hrs) - Revised" in column B are editable in columns C:J. In addition, the Worksheet_Change code will ensure that if new rows are added in column B or if any of the Column B text strings are edited, the data in these rows will be editable only if the value in column B is "Production Plan (Hrs) - Revised".
Because the code contains the protection password, and you wouldn't want your users to see this, you should also protect the VBAProject (the code itself). To do this, go to the Visual Basic Editor and select Tools > VBAProject. Select the Protection tab, check the Lock project for viewing checkbox, and enter a code protection password (make it different from the worksheet protection password).
If I have misunderstood your question, or if the functionality of this code is not as you would have wanted it feel free to follow up. If so, keep in mind that I am on vacation after Tuesday for a week, so try to follow up tomorrow if possible.