Lock row after date column populated

[Lin query]

Hi All

I'm looking for a VBA code to lock rows once a date is entered in column BG of that row. The spreadsheet contains dropdown menus and has a set of grouped hidden columns and is used by various people. So am looking for a macro that is triggered by BG of that row being populated. Any help would be greatly appreciated. Many thanks

 

[John_w]

You need a Workbook_Open event handler in the ThisWorkbook module and a Worksheet_Change event handler in the sheet module of your input sheet, which the code expects to be named "Input Sheet". This sheet is protected with the optional password "abc".

Put this code in the ThisWorkbook module:

Private Sub Workbook_Open()

    Dim lastRow As Long, r As Long
   
    With Worksheets("Input Sheet")   'change sheet name
   
        .Unprotect Password:="abc"  'optional password
       
        'Unlock all cells on sheet
        .Cells.Locked = False
       
        'Lock rows where a date is in column BG
        lastRow = .Cells(.Rows.Count, "BG").End(xlUp).Row
        For r = 1 To lastRow
            If IsDate(.Cells(r, "BG").Value) Then
                .Rows(r).EntireRow.Locked = True
            End If
        Next
       
        'Protect sheet only in UI, which allows Worksheet_Change to lock rows without unprotecting sheet
        .Protect Password:="abc", UserInterfaceOnly:=True  'optional password
       
    End With
   
End Sub

Put this code in the sheet module of your "Input Sheet":

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = Range("BG1").Column Then
        If IsDate(Target.Value) Then
            With Me
                .Rows(Target.Row).EntireRow.Locked = True
            End With
        End If
    End If
End Sub

 

[Lin query]

Many thanks, John W, but unfortunately, this is locking the hidden group columns as hidden, so they can't be revealed without inputting the password. The difficulty is that the spreadsheet firstly has data input by a user, depending on what they've input it may need additional information added by another department, via the hidden group columns, before I transfer all the information onto our database, which should lock that row when the date is entered in column BG, so the information can't be changed. Is there a way of locking columns A - K, and then Z - BG, (the hidden columns L - Y will then remains unlocked) and would this enable the grouped hidden columns to be revealed or hidden without having to unlock the spreadsheet?

 

[John_w]

Specific columns in the input row can be locked, but you still need to unprotect the sheet with the password to expand the hidden grouped columns, in order to enter data in them. You can run a macro to expand or collapse the hidden grouped columns, rather than doing it via the UI.

My previous Workbook_Open code relocked the rows with a date in column BG and reprotected the sheet when the workbook is opened. Here is a better method which doesn't relock the rows.

Put this code in a standard module (e.g. Module1):

Public Sub Unlock_All_Cells()
    With Worksheets("Input Sheet")
        If .ProtectContents Then .Unprotect Password:="abc"
        .Cells.Locked = False
        'Protect sheet only in UI, which allows Worksheet_Change to lock rows without unprotecting sheet
        .Protect Password:="abc", UserInterfaceOnly:=True
    End With
End Sub


Public Sub Expand_or_Collapse_Columns_Group()
    With Worksheets("Input Sheet")
        If .ProtectContents Then .Unprotect Password:="abc"
        If .Columns("L:Y").EntireColumn.Hidden Then
            .Outline.ShowLevels ColumnLevels:=2 'expand
        Else
            .Outline.ShowLevels ColumnLevels:=1 'collapse
        End If
        .Protect Password:="abc", UserInterfaceOnly:=True
    End With
End Sub

New code in the ThisWorkbook module:

Private Sub Workbook_Open()
    With Worksheets("Input Sheet")
        If .ProtectContents Then .Unprotect Password:="abc"
        .Protect Password:="abc", UserInterfaceOnly:=True
    End With
End Sub

New code in the sheet module of your "Input Sheet":

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cols As Variant
    If Target.Column = Range("BG1").Column And Target.Row > 1 Then
        If IsDate(Target.Value) Then
            With Me
                'Lock columns A:K and Z:BG
                For Each cols In Array("A1:K1", "Z1:BG1")
                    .Range(cols).Offset(Target.Row - 1).Locked = True
                Next
            End With
        End If
    End If
End Sub

Run the Unlock_All_Cells macro only once. Then save, close and reopen the workbook.

You can run the Expand_or_Collapse_Columns_Group macro (or assign it to a button) to expand or collapse columns L:Y.