Using VBA to Lock Only a Finished Row

[ChadL]

I want to have each row on a spreadsheet lock when I'm finished filling in the information. Basically after the cell in the last column is populated, I want the row to lock to any further editing without a password. So far I've taken bits and pieces of code and tried to make it work for 1 line before I attempt to loop it through the rest of the spreadsheet. Help with either of these tasks will be greatly appreciated. Here's what I have so far:

Private Sub Worksheet_Change(ByVal target As Range)


If Not Intersect(target, Me.Range("AH2")) Is Nothing Then Run Row_Lock


End Sub


Sub Row_Lock(ByVal target As Range)
If target.Address Is Not "AH2" Then Exit Sub
ActiveSheet.Unprotect "password"
If target <> "" Then
Cells.Select
Selection.Locked = False
Range("A2:AH2").Select
Range("A2:AH2").Locked = True
ActiveSheet.Protect "password"
End If
End Sub

Right now I'm getting an "Argument not optional" error message when I run the code.

 

[Gary McMaster]

Hi Chad,

Your Row_Lock procedure is expecting a range argument that you did not pass to it ("Argument not optional")

Row_Lock(ByVal target As Range)'< range argument

'Removed run & added argument

Private Sub Worksheet_Change(ByVal target As Range)


'Note removed "Run" and added (target) to end of next line
If Not Intersect(target, Me.Range("AH2")) Is Nothing Then Row_Lock (target)

End Sub

Gary

 

[ChadL]

Gary,

I've made the changes you suggested but now I'm getting an 'Object Required' error on the Row_Lock (target) bit

Oh and thank you very much for your help. I appreciate it.

Chad

 

[Gary McMaster]

Since you are using more than one procedure and events that I'm not sure are in the right places it would be easier to troubleshoot if you send a copy of your workbook. I have sent you a private message containing my email address. You could also upload your file to a file sharing site so that anyone else following your thread could get a copy also. Just post a link to the uploaded file in this thread.

In the meantime, here's a sample that works a little differently than what you suggested. Rather than lock cells / row etc, it will not let you select anything on the sheet above / before the "the cell in the last column" without entering a password.

Right click on the tab for the target worksheet.
Select "View Code" from the popup menu.
Paste the code below in the code window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oLast As Range
Dim sPWord As String

Application.EnableEvents = False

'Next code line may not be best way to find last cell
'(UsedRange thinks an empty but formatted cell is used)
Set oLast = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell)

If oLast.Value <> "" And Target.Cells(1).Row <= oLast.Row Then
    GoSub GetPWord
    If sPWord <> "HI" Then 'Change to desired password
        ActiveSheet.Range("A" & oLast.Row + 1).Activate
    End If
End If

Application.EnableEvents = True

Exit Sub

GetPWord:

sPWord = InputBox("Enter password:", "Password")

Return

End Sub

 

[ChadL]

Unfortunately, I cannot upload the sheet to a sharing site as it is against company policy. For the purpose of the question, it is safe to assume that the sheet contains data (both numerical and text) in many rows and columns and has formatting and other macros applied to the used cells. I feel I may have been unclear on exactly what I want to achieve here. I want everything in the row to the left of the cell in the second to last column (sorry I was wrong in my original post) to lock and disallow any editing without the input of a password. The code doesn't need to figure out which column is the second to last one if it can be assigned. For example: Once I populate the second to last cell in the row I'm working in, I want everything in the row to the left of the cell I just completed (including the cell I populated) to be password protected against editing.

 

[Gary McMaster]

Can you send me a copy of your workbook via private email so I can see how the data is structured and where you have placed your code? You can delete most of the rows and change any sensitive / private data to dummy info of the same data type.

I believe I already sent my email address in a private message.

Gary