CODEWITHSUNDEEP
vbaexcel
Carolynn1234
Carolynn1234

Reputation: 11

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

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

Upvotes: 1

Views: 948

Answers (2)

B Slater
B Slater

Reputation: 340

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.

Upvotes: 1

braX
braX

Reputation: 11755

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.

Upvotes: 0

Related Questions