auto Lock cells after due date

[Sophia2]

Hello everyone, I have a worksheet with two sheets. one containing the monthly data of personnel

ID#

Name

January

February

March

April

May

June

July

August

September

October

November

December

Sales

Michelle

1257​

5827​

5465​

65451​

51321​

132​

3212​

Cost

John

4254​

5952​

614​

5416​

18545​

3212​

1587​

and another sheet with due date of encoding in each month

Month	Due date
January	8-Feb-23​
February	8-Mar-23​
March	8-Apr-23​
April	9-May-23​
May	9-Jun-23​
June	10-Jul-23​
July	10-Aug-23​
August	10-Sep-23​
September	10-Oct-23​
October	10-Nov-23​
November	10-Dec-23​
December	10-Jan-24​

how to auto lock the column in the data sheet of months with lapsed due date.

for example... today is Sep 8, 2023. therefore, when i open the file, the data columns for January to July should be lock and cannot be edited.
I can only encode or edit in columns for August to December.
On September 11, the august column will also be locked.

I believe a VBA will work for this. But, I am not good with VBA and macro except in recording only.

Thank you in advance for any help on this one.

 

[sxhall]

Hi Sophia2,

This can be done with VBA, code below.

This will need to run automatically when the workbook is opened. To do this the code needs to be added to the workbook module, see image. Once saved this will run when the workbook is opened and lock any cells in the monthly columns based on the dates in the 'Due date' column.

To make sure that users are unable to type in the protected cells I have included a password to protect the sheet. However, to make this secure you will need to password protect access to VBA as a user would be ale to access the code to see the password as well!

Private Sub Workbook_Open()

Dim StrMnth As String
Dim EndMnth As String
Dim DateNow As Date

Sheets("data").Unprotect Password:="SecurePassword!"

With Sheets("data").Range("Table2[ [January]:[December] ]")
.Locked = False
End With

For Each i In Range("Table1[Due Date]")

If i.Value <= Date Then
    If StrMnth = "" Then
    StrMnth = i.Offset(, -1).Value
    Debug.Print "Start " & StrMnth
    End If
    EndMnth = i.Offset(, -1).Value
    Debug.Print "End " & EndMnth
End If

Next i

With Sheets("data").Range("Table2[ [" & StrMnth & "]:[" & EndMnth & "] ]")
.Locked = True
End With

Sheets("data").Protect Password:="SecurePassword!"

End Sub

EDIT: PS forgot to add I have the tables named as 'Table1' for your month and due date and 'Table2' is the 'Id', 'Name' and Monthly figures table.

 

[Sophia2]

Hi Sophia2,

This can be done with VBA, code below.

This will need to run automatically when the workbook is opened. To do this the code needs to be added to the workbook module, see image. Once saved this will run when the workbook is opened and lock any cells in the monthly columns based on the dates in the 'Due date' column.

To make sure that users are unable to type in the protected cells I have included a password to protect the sheet. However, to make this secure you will need to password protect access to VBA as a user would be ale to access the code to see the password as well!

Private Sub Workbook_Open()

Dim StrMnth As String
Dim EndMnth As String
Dim DateNow As Date

Sheets("data").Unprotect Password:="SecurePassword!"

With Sheets("data").Range("Table2[ [January]:[December] ]")
.Locked = False
End With

For Each i In Range("Table1[Due Date]")

If i.Value <= Date Then
    If StrMnth = "" Then
    StrMnth = i.Offset(, -1).Value
    Debug.Print "Start " & StrMnth
    End If
    EndMnth = i.Offset(, -1).Value
    Debug.Print "End " & EndMnth
End If

Next i

With Sheets("data").Range("Table2[ [" & StrMnth & "]:[" & EndMnth & "] ]")
.Locked = True
End With

Sheets("data").Protect Password:="SecurePassword!"

End Sub

View attachment 98426

EDIT: PS forgot to add I have the tables named as 'Table1' for your month and due date and 'Table2' is the 'Id', 'Name' and Monthly figures table.

Hi sxhall! thank you so much for the help.
i copied your code and placed it in "thisworkbook" as you illustrated.
but i ran into this error

when i run debug

i also followed your table names.

and the columns month can still be edited.
where did i go wrong?

I really appreciate your response

 

[Sophia2]

Hello again sxhall!

i just knew i made a mistake.
my due dates were not formated as dates.
after changing them to date format, your code worked!

thank you so much for the help!!!