Lock Excel Columns by Date

Question

I'm trying to create a simple code for locking cell columns based on the date. I have dates (every Friday) setup from Cells C1 to BA1. The code I'm using is as follows and seems to be locking everything or it gives me an type-mismatch error.

Private Sub worksheet_selectionChange(ByVal Target As Range)

If Range("C1:BA1").Value < Date Then
    ActiveSheet.Protect Password:="1234"
ElseIf Range("C1:BA1").Value > Date Then
    ActiveSheet.Unprotect Password:="1234"
    ActiveSheet.EnableSelection = xlNoRestrictions
End If
End Sub

Any help would be greatly appreciated.

Thanks!! -D

Answer 1

Protect and Unprotect act on the sheet, not on the range. You do need to unlock the sheet, but then you need to set the Range.Locked property to true/false, before re-locking it. Also this requires a loop, you cannot act on the full range at once to achieve this result.

I would recommend putting this in Workbook_Open, so it only runs once, unless you are changing the dates; then is should go in Worksheet_Change.

Private Sub Workbook_Open()

    Dim col As Range

    'Set the correct sheet name here:
    With ThisWorkbook.Sheets("Sheet1")

        .Unprotect "1234"

        For Each col In .UsedRange.Columns

            col.EntireColumn.Locked = col.Range("A1").Value < Date

        Next col

        .Protect "1234"

        .EnableSelection = xlNoRestrictions

    End With

End Sub