How to protect a row of data once information is confirmed

[SCOTLAND]

How do I go about protecting a row of data automatically once it has been confirmed as accurate? I'll explain a bit of what I'm looking for. Several users currently enter data to a shared spreadsheet. That data is verified in accounting and once done so the row is marked with an "x" to indicate agreement and posting to the GL. How would I get Excel to automatically password protect these rows marked with the "x" upon exiting the file. Any suggestions?? I don't want to manually select rows. Thanks.

 

[DRJ]

Hi - Welcome to the board

Try this before save code. You can do it for before close, but you would have to save to make sure the changes were kept.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim x As Long
Dim y As Long

    Sheets("Sheet1").Unprotect Password:="Password"
    y = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    For x = 1 To y
        If Range("A" & x).Value = "x" Then Range("A" & x).EntireRow.Locked = True
    Next x
    Sheets("Sheet1").Protect Password:="Password"

End Sub

 

[erik.van.geit]

Re: How to protect a row of data once information is confirm

there must be data in column A, else change the code
this code locks the last row and if you want unlocks the following row
so there will only be one row where data can be put in

Sub protectlastrow()
ActiveSheet.Unprotect
Range("A65536").End(xlUp).EntireRow.Locked = True
Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Locked = False
ActiveSheet.Protect
End Sub

regards,
Erik