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

alexgeorge

New Member
Joined
Nov 18, 2009
Messages
7
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

Board Regular
Joined
Jul 20, 2012
Messages
161
"Locking" a cell only prevents it from being edited if the sheet is protected, I believe. But anyway, here's this:
Code:
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

New Member
Joined
Nov 18, 2009
Messages
7
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

Board Regular
Joined
Jul 20, 2012
Messages
161
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:

Code:
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:

Code:
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.
 
Last edited:

alexgeorge

New Member
Joined
Nov 18, 2009
Messages
7
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)

Code:
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

Board Regular
Joined
Jul 20, 2012
Messages
161
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
 
Last edited:

memar_one

Board Regular
Joined
Jul 20, 2012
Messages
161
Oops! Forgot quotes:

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

alexgeorge

New Member
Joined
Nov 18, 2009
Messages
7
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

Board Regular
Joined
Jul 20, 2012
Messages
161
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.

Code:
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

Board Regular
Joined
Jul 20, 2012
Messages
161
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)

Code:
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
 

Forum statistics

Threads
1,082,334
Messages
5,364,681
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top