Lock specific group of cells after data is entered in one cell

Question

I need to use VBA code to lock a specific set of cells once data is entered in ONE cell. Context: The user will be going along a row entering user name, pass, etc. Some cells in the row are locked and some are unlocked for where they need to enter data, but once they answer their data in the last cell, I want all of the previously unlocked cells in that row to lock.

I'm having trouble getting this to work without it constantly updating and locking the cells over and over. This is what I have right now.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    ActiveCell.Select
    If Range("O22") <> "" Then
        ActiveSheet.Unprotect
        Range("F22,G22,J22,K22,L22,O22").Select
        Selection.Locked = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If

End Sub

**Never** use ActiveCell in a Worksheet_Change or Workbook_SheetChange event procedure. Not only is it typically inaccurate but cell values can be changed by other means than typing. — , Aug 04 '17 at 15:21

B Slater · Answer 1 · 2017-08-04T15:42:11.197

1

If you want the workbook to automatically lock that range if "O22" is not empty then this is the code you'd need

Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if O22 has changed before running rest of code
    If Not Intersect(Target, Range("O22")) Is Nothing Then
        'If the final column isn't empty then
        If Range("O22") <> vbNullString Then
            'Unprotect the sheet
            Me.Unprotect
            'Lock the target cells
            Me.Range("F22,G22,J22,K22,L22,O22").Locked = False
            'Reprotect the sheet
            Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End If
    End If
End Sub

In general in VBA you should avoid ever using 'Select' or 'Activate' and instead reference the objects, see this question.

It's hard to judge exactly the structure of your workbook but if you copy this code into a 'Sheet' code module then it should work for the sheet in question, if you need it to work on more of a workbook-level then please give more details of what's needed.

edited Aug 04 '17 at 15:42

answered Aug 04 '17 at 15:26

B Slater

330
2
12

1

I'd change it to `Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)` in `ThisWorkbook` and update the `Me` to `Sh` so it works on any sheet and maybe swap `Range("O22")` for `Target` if the OP wants to check the cell they changed. Or maybe wrap it in `If Not Intersect(Target, Range("O22")) Is Nothing Then` if it needs to be O22 that is changed. All possible alternatives, so depends what OP wants. – Darren Bartrup-Cook Aug 04 '17 at 15:35
1

Intersect is more performant definitely, no point doing it all when O22 hasn't even changed, I just threw this together a little quickly, I'll amend it now! I figured the OP would only want this happening on one sheet of his workbook because I think it'd be a strange workbook if every single sheet was set up to be a login screen but it's hard to judge without a little more info. – B Slater Aug 04 '17 at 15:39

score 0 · Answer 2 · answered Aug 04 '17 at 15:20

Sounds to me like you want something more like this (notice it's in a different event too)

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 20 Then
    Range("F22,G22,J22,K22,L22,O22").Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  End If

End Sub

Change the 20 to whatever column you want the code to run on its change.

Lock specific group of cells after data is entered in one cell

2 Answers2