Lock Column
 

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

comparing dates that way would be inaccurate. to illustarte from the
immediate window:

? "01/12/2005" < "03/01/1995"
True


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.Value2 < clng(Date) - 1 Then
cell.EntireColumn.Locked = True
Else
cell.EntireColumn.Locked = False
End If
Next
ActiveSheet.Protect Password:="mypass"
End Sub

--
Regards,
Tom Ogilvy

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

In the future, please do NOT attach workbooks to this ng.

--
Don Guillett
SalesAid Software

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

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

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

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

Tom,
Thanks a lot for clarifying it, i tested it now it works for every date and
column.

Regards,
Shobhit Bhatnagar



"Tom Ogilvy" wrote in message
...
comparing dates that way would be inaccurate. to illustarte from the
immediate window:

? "01/12/2005" < "03/01/1995"
True


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.Value2 < clng(Date) - 1 Then
cell.EntireColumn.Locked = True
Else
cell.EntireColumn.Locked = False
End If
Next
ActiveSheet.Protect Password:="mypass"
End Sub

--
Regards,
Tom Ogilvy

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