VBA lock an active row

[Pinaceous]

Hi All,

I would like to after an active row has been filled that VBA will automatically locks that row from being changed in the future.

Can someone help me with this coding?

Thanks,
Pinaceous

 

[Joe4]

I think you will need to define what it means to "be filled".
Namely, which column(s) need to be filled?
Also, how exactly are these columns being filled? Manually? By copy/paste? By formula?

 

[Pinaceous]

Hi Joe,

Thanks for responding.

Okay, I have Sheet1 whereby I have a user filling in one row starting from row's 7 - 21 from Columns A to L.

I would like when the user has completed their row that that row then becomes locked in not allowing the next user to tamper with its information that was written there.


Thank you,

Paul

 

[JoeMo]

Does each user fill a different row?

 

[Pinaceous]

Yes, each user will fill out one of these (their) row "starting from row's 7 - 21 from Columns A to L".

At this point in time this is not a shared document, so each user will have the opportunity to input information exclusively.

 

[JoeMo]

Maybe something like the selection_change event macro below. This would handle inadvertent changing of another user's row. Would need some adjustment to prevent willful changes while still permitting a legitimate user to edit his row after filling in all the cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, R As Range, ans As Long
Set R = Range("A7:L21")
For Each c In Target
    If Not Intersect(c, R) Is Nothing Then
        If Application.CountA(R.Rows(c.Row - R.Rows(1).Row + 1)) = R.Columns.Count Then
            ans = MsgBox("Row " & R.Rows(c.Row - R.Rows(1).Row + 1).Row & " is filled and locked. If its your row, choose Yes to open it for editing", vbYesNo)
            If ans = vbYes Then Exit Sub
            Application.EnableEvents = False
            R(1, 1).Offset(-1, 0).Select
            Application.EnableEvents = True
            Exit Sub
        End If
    End If
Next c
End Sub

 

[Pinaceous]

Thank you Joe!

 

[JoeMo]

Thank you Joe!

You are welcome - thanks for the reply.