Help locking column on date value

  • Thread starter Thread starter Shobhit Bhatnagar
  • Start date Start date
S

Shobhit Bhatnagar

Hi,
I want to lock/protect column of a particular date once the date changes. I tried doing it with different vba codes but no use. Eg. if the date is 25th October,2004,
the column with the same date should get locked/protected once the date system date changes.
Thanks in advance for your help.

Shobhit Bhatnagar

Date 10/1 10/2 10/3 10/4 10/5 10/6 10/7 10/8 10/9 10/10 10/11 10/12 10/13 10/14

Parameters
a
b
c
d
e
f
 
Private Sub Workbook_Open()
Dim cell as Range
Worksheets("Schedule").Activate
for each cell in Rows(1).Cells
if cell.Text = Format(date,"mm/d")
activeSheet.Unprotect
cell.EntireColumn.Locked = True
activeSheet.Protect
exit for
end if
Next
End Sub

Put this is in the workbook.open event
[Chip Pearson's page on events - http://www.cpearson.com/excel/events.htm]

See help on the protect and unprotect methods for arguments (like password).

--
Regards,
Tom Ogilvy


Hi,
I want to lock/protect column of a particular date once the date changes. I
tried doing it with different vba codes but no use. Eg. if the date is 25th
October,2004,
the column with the same date should get locked/protected once the date
system date changes.
Thanks in advance for your help.

Shobhit Bhatnagar

Date10/110/210/310/410/510/610/710/810/910/1010/1110/1210/1310/14

Parameters
a
b
c
d
e
f
 
Thanks for your reply. I tried the code it does not work. What i am
trying to achive is to protect the column based on the date value. The
columns which are less than current system date should get protected.
Through this code i tried to the whole sheet get protected.

Thanks for the solution in advance.

Shobhit Bhatnagar
 
You need to understand protection. Cells are locked or unlocked - which is
meaningless unless the sheet it protected.
 
Thanks Tom,
A bit of tweaking with your code now it works.

Private Sub Workbook_Open()
Dim cell As Range
Worksheets("Sheet1").Activate
ActiveSheet.Unprotect Password:="abc"
For Each cell In Rows(1).Cells
If cell.Text < Format(Date, "dd/mm/yyyy") Then
cell.EntireColumn.Locked = True
Else
cell.EntireColumn.Locked = False
End If
Next
ActiveSheet.Protect Password:="abc"
End Sub

Thanks
 
Back
Top