VBA code to turn on cell protection for a row, based 'Yes' value in one cell - applied to all rows in table

[alexgeorge]

Excel 2010
Data formatted as a table
One user inputs data into the table for a charge they are making. Our finance person later reconciles the charge by changing the cell in the 'Invoiced' column to 'Yes' and entering an invoice #. I'd like to have VBA code that changes the cells to locked so the regular user can't make edits to the line item after the finance person reconciles it.

• I have the VBA code below to change cells a8:g8 to be 'locked' (and thus uneditable) since the cell in the invoiced column = Yes.
• Cells a9:g9.... should be unlocked (and thus still editable) since the invoiced column <> Yes
• My code below works fine for one row, but wondering how I to edit it so it applies to all rows in the table, especially considering we may dynamicly extend the table to add more rows at the bottom

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("f8").Value <> Yes Then
Range("a8:e8").Locked = True
Else
Range("a8:e8").Locked = False
End If
End Sub

Thank you in advance for the help.

Sincerely, Alex

 

[memar_one]

"Locking" a cell only prevents it from being edited if the sheet is protected, I believe. But anyway, here's this:

Private Sub Worksheet_Change(ByVal Target As Range)
For each C in Range("F8", Range("F" & ActiveSheet.Rows.Count).End(xlUp)
If C.Value = "Yes" Then
Range("a" & c.Row, "g" & c.Row).Locked = True
Else
Range("a" & c.Row, "g" & c.Row).Locked = False
End If
Next
End Sub

It can also be changed so that it'll only lock when you change something in column F.
Right now, it'll run the macro whenever you change ANYTHING (perhaps not a good use of the code).

 

[alexgeorge]

Thanks so much for your response.

"Locking" a cell only prevents it from being edited if the sheet is protected, I believe.

The sheet protection is 'on' by default, so the locked cells cannot be changed. The finance person has the password to unprotect the sheet, and perform the reconciliations turning the Invoiced cell to 'Yes'. This code should automatically change the cells in that row to locked. When the finance person is done making the edits, they will turn sheet protection back on.

It can also be changed so that it'll only lock when you change something in column F. Right now, it'll run the macro whenever you change ANYTHING (perhaps not a good use of the code).

That is a great idea. Does the code you provided do that?

I'm not great in VBA so maybe I've done something incorrectly, but when I tried the code you gave, I received the error below.

 

[memar_one]

Oops forgot a parenthesis on the end of that...

But since you want the macro to only work when you change column F (much more efficient), change it to:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub

If Target.Value = "Yes" Then 
Range("a" & Target.Row, "g" & Target.Row).Locked = True 
Else 
Range("a" & Target.Row, "g" & Target.Row).Locked = False 
End If 

End Sub

This will affect changes made that aren't results of calculations (like it won't lock cells if you fill the word "Yes" to the right or something, or have a formula that automatically changes it, instead of inputting it, which I don't think will happen.)

Just in case you do want the macro to run any time anything is changed (that way you can make the "Yes" cells conditional on other things) then you can run this macro instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C as Range

For Each C in Range("F8", Range("F" & ActiveSheet.Rows.Count).End(xlUp).Address)

If C.Value = "Yes" Then 
Range("a" & C.Row, "g" & C.Row).Locked = True 
Else 
Range("a" & C.Row, "g" & C.Row).Locked = False 
End If 

Next

End Sub

The first code will be much faster for large data sets though. Much faster, and it's also what you're looking for.

 

[alexgeorge]

Oops forgot a parenthesis on the end of that...

But since you want the macro to only work when you change column F (much more efficient), change it to:

This will affect changes made that aren't results of calculations (like it won't lock cells if you fill the word "Yes" to the right or something, or have a formula that automatically changes it, instead of inputting it, which I don't think will happen.)

I do prefer 'more efficient' methods, and your code worked well. The only change I made was to apply the locked true/false only to columns a:e, since I didn't want a lack of a 'yes' in column f to remove locked for cells in columns f or g (those should always remain locked)

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub


If Target.Value = "Yes" Then
Range("a" & Target.Row, "e" & Target.Row).Locked = True
Else
Range("a" & Target.Row, "e" & Target.Row).Locked = False
End If


End Sub

Thank you very much for the help. You have addressed my question and the solution meets my current needs.

If you have more time, I'd like to pose the following add on question. If you don't have time, don't worry about it.

It appears that the code you provided looks throughout column F in the entire worksheet. What if I wanted to direct the code to look at the column in a specific table on the worksheet? This might be helpful considering that columns might shift (moving the table on the worksheet, or adding columns to the left of F), or in the case where I might have another data set above or below the table that shouldn't be affected by the code.

My table shown above is named Spender_Example and the column name is Invoiced: =Spender_Example[Invoiced]

Is there a way to edit the code above to apply to =Spender_Example[Invoiced] rather than column F?

Thanks again for you help

 

[memar_one]

Change this line:
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub

To this:
If Intersect(Target, Spender_Example[Invoiced]) Is Nothing Then Exit Sub

 

[memar_one]

Oops! Forgot quotes:

If Intersect(Target, Range("Spender_Example[Invoiced]")) Is Nothing Then Exit Sub

 

[alexgeorge]

Oops! Forgot quotes:

If Intersect(Target, Range("Spender_Example[Invoiced]")) Is Nothing Then Exit Sub

Wow - great! Thanks again.

1) That change for 'Intersect' answered my question about where the code was looking for the 'Yes'. It doesn't address the possible change in where to apply the lock true/false. The code just applies flatly to columns a:e of the row that contains the 'yes'. Is there a way to dynamically have the code apply to specific columns in the table?

2) Also, the code doesn't seem to dynamically update when I duplicated the spreadsheet? For example, if I duplicate the spreadsheet 'Example' to 'Example (2)', the newly created table updates to a table name of 'Spender_Example2' but the code on that page doesn't update to be looking at the new table 'Spender_Example2'

I guess those 2 questions would be addressed/solved if I simply return to our earlier code that only looked at column F. Would that be your recommendation?

Regards, Alex

 

[memar_one]

This will lock all the cells in the row of the table that you changed... It will also use any table on the current sheet (only 1 though)

Can you give me the titles of the columns that you DON'T want locked? That way I can take them out of the locked area.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTable As ListObject

On Error Goto ErrHandler
Set myTable = ActiveSheet.ListObjects(1)
On Error Goto 0

 If Intersect(Target, Range(myTable.name & "[Invoiced]")) Is Nothing Then Exit Sub

 If Target.Value = "Yes" Then
Intersect(Rows(Target.Row), Range(myTable.name & "[#All]")).Locked = True
Else
Intersect(Rows(Target.Row), Range(myTable.name & "[#All]")).Locked = False
End If

ErrHandler:
End Sub

 

[memar_one]

This will lock all the cells in the row of the table that you changed... It will also use any table on the current sheet (only 1 though)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTable As ListObject

On Error Goto ErrHandler
Set myTable = ActiveSheet.ListObjects(1)
On Error Goto 0

 If Intersect(Target, Range(myTable.name & "[Invoiced]")) Is Nothing Then Exit Sub

 If Target.Value = "Yes" Then
Intersect(Range("A" & Target.Row, Target.Offset(0, -1)), Range(myTable.name & "[#All]")).Locked = True
Else
Intersect(Range("A" & Target.Row, Target.Offset(0, -1)), Range(myTable.name & "[#All]")).Locked = False
End If

ErrHandler:
End Sub