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