PDA

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: