Lock Column
 

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

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

Lock Column
 

Would you get the same results if the cells were actually formatted
d/mm/yyyy

? "7/05/2005" < "13/05/2005"
False

He stated the problem:

It works but only
sometimes.

You don't think a string comparison of dates in d/m/y order is a likely
source of the problem?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
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