You are here:

Excel/Unprotecting dynamic rows in a protected worksheet

Advertisement


Question
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.


https://skydrive.live.com/redir?resid=A1B8470E44E28CE2!144&authkey=!AMmQq1D4vCEcXEo

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?

or

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?

d. calculations?

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".

Damon

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

Regards,
Prabhat

Answer
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
  
  Me.Unprotect Password:="password"
  If Cells(TRow, "B") Like "*Production Plan (Units) - Revised*" Then
     Range(Cells(TRow, "C"), Cells(TRow, "J")).Locked = False
  Else
     Range(Cells(TRow, "C"), Cells(TRow, "J")).Locked = True
  End If
  Me.Protect Password:="password"
  
End Sub

Sub CheckAllRows()
  'Checks column B of all rows and locks if they contain "Production Plan (Units) - Revised"
  Dim iRow       As Long
  Dim LastRow    As Long
  
  Me.Unprotect Password:="password"
  
  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
     End If
  Next iRow
  
  'uncomment this if you want this procedure to protect the sheet
  Me.Protect Password:="password"
  
End Sub

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.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.