Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
how do I lock column and row names on spreadsheet | Excel Worksheet Functions | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions |