Select a range based on system date

[tewsuave]

Part 1

I have a spreadsheet that I want to open protected (locked). However,
I want to unlock a specific range for data entry based on the day of
the actual system date.

For example if today is 11/5/08 then we want to unlock row 5,
specifically cells B5:I5.

I have experimented with several functions, which return the desired
results, but I am not skilled enough to place them into a well-written
VBA subroutine.

Please note the following function examples that return the start and
ending cells of the desired range.

=ADDRESS(DAY(NOW()),2) this will display the start cell of the range
(B5) and=ADDRESS(DAY(NOW()),9) will display the last cell in the range
(I5).

If anyone could assist with the writing of a subroutine that would
select the whole range, for example;

ADDRESS(DAY(NOW()),2).ADDRESS(DAY(NOW()),9).Select

That would be the first step to my solution.

Part 2

Tying it all together...

Once the range can be selected based on the system date, through code,
then the next task is to either unlock that range for editing or
better yet, utilize the menu command Tools, Protection, Protect and
Share Workbook and should read menu command Tools, Protection, Allow
Users to Edit Ranges

So we can allow specific users based on their Active Directory Account
to edit a particular range.

However, at a minimum, opening the sheet unlocked (protected) and
saving it locked (protected) would work.

Thanks in advance for staying up at night working on this.

 

[Gary''s Student]

Sub UnlockUm()
Dim s1 As String
Dim s2 As String
s1 = Evaluate("ADDRESS(DAY(NOW()),2)")
s2 = Evaluate("ADDRESS(DAY(NOW()),9)")
Range(s1 & ":" & s2).Locked = False
End Sub

 

[Dave Peterson]

Option Explicit
Sub testme()
With ActiveSheet
.Unprotect Password:="topsecret"
.Cells(Day(Date), "B").Resize(1, 8).Locked = False
.Protect Password:="topsecret"
End With
End Sub

But this won't work if the workbook is shared.

You can't change the protection of a worksheet in a shared workbook.

 

[Rick Rothstein]

I think this might be a simpler way to code it...

Sub UnlockUm()
Range(Cells(Day(Now), 2), Cells(Day(Now), 9)).Locked = False
End Sub