Locking a row after info entered

[newtoexel79]

Hello all,


We currently have a spreadsheet which we use to track all incoming material. The first 7 columns are unlocked and used to enter the data about the material. The 8th column I now have set that it is locked until the button
"AI Approval" is clicked and the password entered. Once the password is entered then our third party inspector can sign off on the materials. What I am trying to do is once the inspector puts their initials and date into the AI Approval column (I) and saves, then the rest of that row is locked and cannot be changed. So if you look at the top row of the example, columns A thru G would be unlocked as long as column I (AI Approval) is not filled out. Once column I is filled out then columns A thru G (row 1) would then be locked and you could not alter anything in them. The purpose is to ensure that once our third party inspector verifies the material, no one can go back in and change it.

Thank you in advance!!

Current Code:

Module 1-

Sub CommandButton3_Click()
  Dim pwd As Variant
 
  pwd = InputBox("Please Enter password", "EDIT AI COLUMN")
 
  If StrPtr(pwd) = 0 Then
    MsgBox "Cancelled"
  ElseIf Len(pwd) = 0 Then
    MsgBox "Please Enter a valid value"
  Else
    If pwd = "HSB1866" Then
      With Sheets("Pipe-Tube")
        .Unprotect "HSB1866"
        .Range("I8:I1000").Locked = False
        .Protect "HSB1866"
      End With
      MsgBox "Now you can edit the AI column"
    End If
  End If


 
End Sub

ThisWorkbook -

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  With Sheets("Pipe-Tube")
    .Unprotect "HSB1866"
    .Range("I8:I1000").Locked = True
    .Protect "HSB1866"
  End With
End Sub

 

[RustamMika]

Hi
Use Workbook_BeforeSave event..

for example:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Cells.Locked = False
    lr = Cells(Rows.Count, "I").End(xlUp).Row
    Range("A1:I" & lr).Locked = True ' replace A1 with cell address, where you table started..
    ActiveSheet.Protect "HSB1866"
End Sub