Lock row when data entered into cells in specific column

partsbbw

New Member
Joined
Jan 18, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I would like a VBA code to lock a the row I am working in when data is entered into a cell in column I.
So I will enter data in cells A1:H1 and when I enter data into I1 it will then lock the row so it cannot be changed.
I then would like that to continue for every proceeding row that I enter data. The sheet will likely only have data entered up to row 25.
Any help is greatly appreciated.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try this:
The sheet must be protected, change "abc" to the password of your sheet.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("I:I")) Is Nothing Then
    ActiveSheet.Unprotect "abc"
    Range("A" & Target.Row & ":I" & Target.Row).Locked = True
    ActiveSheet.Protect "abc"
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
 
Upvote 0

Forum statistics

Threads
1,214,987
Messages
6,122,614
Members
449,090
Latest member
vivek chauhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top