View Full Version : Solved: Protect Column if it is not equal to today's date
jammer6_9
01-15-2008, 02:46 AM
From Column A to Column AE in Row 1 dates were there Jan1 to Jan31. Now what I want to do is If Date in the Column is not equal to today's date then column will be protected.
Sub ProtectColumn()
Dim lngColumn As Long, lngLastCol As Long
lngLastCol = Range("AE1").End(xlToLeft).Column
For lngColumn = lngLastCol To 1 Step -1
If lngColumn <> Now() Then 'today's date
'protect columns
End If
Next lngColumn
End Sub
Bob Phillips
01-15-2008, 03:03 AM
Sub ProtectColumn()
Dim lngColumn As Long, lngLastCol As Long
With ActiveSheet
.Unprotect
lngLastCol = .Range("AE1").End(xlToLeft).Column
.Cells.Locked = False
For lngColumn = lngLastCol To 1 Step -1
If .Cells(1, lngColumn).Value <> Date Then 'today's date
.Columns(lngColumn).Locked = True
End If
Next lngColumn
.Protect
End With
End Sub
jammer6_9
01-15-2008, 03:21 AM
thnks once again for the quick reply xld. BUT the code protect column A which is not the date of today.
Bob Phillips
01-15-2008, 03:25 AM
Not for me it didn't. What is in A1, B1, etc.?
jammer6_9
01-15-2008, 04:50 AM
A1 is JAN-1 B1 is JAN-2... It's dates actually until AE1 which is JAN-31...
jammer6_9
01-15-2008, 05:22 AM
I guess I have found the solution... It's because on this line... Thanks xld...
For lngColumn = lngLastCol To 256
Aussiebear
01-15-2008, 06:41 AM
I guess I have found the solution... It's because on this line... Thanks xld...
For lngColumn = lngLastCol To 256
How does this now make it work?
jammer6_9
01-15-2008, 07:00 AM
Find attache. When the worksheet activate, all columns will be protected excluding the column that has the date of today... :dunno
Aussiebear
01-15-2008, 07:27 AM
Thank you for the sheet, but why did you chose 256? If you only have 31 columns to protect, why not just use
For lngColumn = lnglastCol to 31
jammer6_9
01-15-2008, 07:33 AM
Yeah I only have 31 for the mean time because i was just trying to have 1 month but as it seems the code works, i took all columns of an excel 2003 which is 256. Might put more dates on the columns in the future.
Aussiebear
01-15-2008, 07:36 AM
Okay.:friends:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.