Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to lock a column | Excel Discussion (Misc queries) | |||
Lock Column | Excel Discussion (Misc queries) | |||
Lock Column | Excel Programming | |||
Lock Column | Excel Programming | |||
Protect or Lock Column | Excel Programming |