Lock Cells After Entry

[pwielgosh]

Hi,

I've browsed the forums and tried searching Google but am unable to come up with the information that I need. I know very little VBA and would like to lock certain cells within a worksheet. My worksheet name is Receipt List. Data will potentially be entered in A4 through AO5000. I would like a vba code that once information is entered in column J it will lock all the cells in that row in columns A through J and do this for each row.

Once this worksheet is implemented data will start to be entered in A4 and will continually go down. So every time information is entered in column J I want everything in that row and above to remain locked unless a password is used. Another user will be entering information in columns K through AO so I do not want to have to enter a password each time and these cells do not contain information that need to be locked.

Any help on this would be greatly appreciated!

Thank you!

 

[Joe4]

Locked cells only work if both:
- the cells are locked
- the sheet is protected

By default, all cells on an Excel file are locked, but the sheet is not protected. So the first thing you have to do is unlock all the cells you want to be able to enter data into initially (this may be whole sheet, or just certain sections of it). So:
1. Select the range you wish them to be able to type information into.
2. Right click and select Format Cells
3. Go to the Protection tab
4. Uncheck the Locked Box

Now, password Protect your Sheet.
1. Go to the Review tab
2. Click Protect Sheet
3. Make sure the first two items are checked
4. Enter a password (and remember it!)
5. Click OK.

Now, we want to use Event Procedure VBA code that will fire whenever a cell in column J is updated. So do the following:
1. Right click on the Worksheet tab name at the bottom of your screen
2. Select "View Code"
3. Enter the code below into the VB Editor window
4. Change the two references to "password" to the password that you have chosen
5. Save the code

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if entry made in column J
    If Target.Count = 1 And Target.Column = 10 And Target.Row >= 4 And Len(Target) > 0 Then
        ActiveSheet.Unprotect Password:="password"
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Locked = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
    End If

End Sub

This should now do what you want.

 

[pwielgosh]

That worked perfectly...thank you so much for all of your help! It is greatly appreciated.

 

[Joe4]

Glad I could help!