Lock Column
Correction: As Tom pointed out, it won't work consistently using DD/MM/YYYY
format. I should have checked my test more carefully!
"Toppers" wrote:
Hi,
I tried your code and it works OK. I opened/closed a workbook
several times, added new dates, saved and re-opened. All the columns were
protected. So what exacly doesn't work?
Note your code will protect empty cells - is this required?
XL2003
"Shobhit Bhatnagar" wrote:
I have created a worksheet(May_05), in which i am trying to lock the columns
based on date values, if the first row of the colums has a date lower than
the current date the whole column should get locked, It works but only
sometimes. Following is the code i am using.
Private Sub Workbook_Open()
Dim cell As Range
Worksheets("May_05").Activate
ActiveSheet.Unprotect Password:="mypass"
For Each cell In Rows(1).Cells
If cell.Text < Format(Date - 1, "dd/mm/yyyy") Then
cell.EntireColumn.Locked = True
Else
cell.EntireColumn.Locked = False
End If
Next
ActiveSheet.Protect Password:="mypass"
End Sub
Please let me know if i am doing anything wrong or is there a better way to
achieve the same. I am also attaching my workbook for the same.
Thanks for your help in advance.
Shobhit Bhatnagar
|