VBA How to lock only certain columns in a sheet based on date

Asked Feb 11 '19 at 17:59

Active Feb 12 '19 at 07:45

Viewed 46 times

Essentially I have a date field atop every column in may data that corresponds to the first of each month. There are several steps in this process that have already been completed but the last step is to lock every column that has a date value that is before the current date. The data is structured this way:

|43282   |43313   |43344   |43374    |43405    |43525   |
|7/1/2018|8/1/2018|9/1/2018|10/1/2018|11/1/2018|3/1/2019|
|$125    |$137    |$654    |$876     |$136     |$125    |

and so on. The code I am trying to use is as follows:

Sub LockDateCols()

Dim i As Range
For Each i In Sheets("Sheet1").Range("F1:AS1").Cells
    If i.Value < Int(CDbl(Now())) Then
        i.EntireColumn.Locked = True
    End If
Next i
End sub

Where F1:AS1 is the row of cells that contains the numeric date values. However when I execute this code it doesn't actually lock anything. What am I missing? In the example provided I want the rightmost column to be editable while locking the other 5 so just locking the whole sheet wouldn't work.

edited Feb 12 '19 at 07:45

Pᴇʜ

56,719
10
49
73

asked Feb 11 '19 at 17:59

DBA108642

1,995
1
18
55

2

You need to protect the sheet. – Comintern Feb 11 '19 at 18:00
To expand on what @Comintern said, changing the `Locked` property of a cell to `True` or `False` just indicates whether you can edit that cell or not when the worksheet is protected. – dwirony Feb 11 '19 at 18:03
@Comintern So if i lock the whole sheet and just change the Locked property to false those cells will still be editable? – DBA108642 Feb 11 '19 at 18:05
2

Yep, pretty much. – Comintern Feb 11 '19 at 18:06
@Comintern thanks, you should submit it as an answer so I can accept it for you. – DBA108642 Feb 11 '19 at 18:15

VBA How to lock only certain columns in a sheet based on date

0 Answers0

Linked