Excel 2003 VBA Assistance needed

[Tymothé]

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]

Part1 is quite easy:

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

 

[Phillip]

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.

from Phillip London UK

Copy this code into the Thisworkbook module in the VBA Editor
A quick way to get to the Thisworkbook module is to right click the
Excel icon to the left of the File menu option and select View Code

This will select the range to be edited based on the day but
not allow the user to select any other cells
Save your workbook close it and reopen it for the code to work


Private Sub Workbook_Open()
Dim theday As Integer
Worksheets("sheet1").Select
theday = Day(Date)
SetRange theday
End Sub


Sub SetRange(rw As Integer)
Dim rng As Range
Set rng = Range("B" & rw & ":I" & rw)
rng.Select
With ActiveWindow
.ScrollColumn = 1
.ScrollRow = rw
End With
Worksheets("sheet1").ScrollArea = rng.Address
End Sub

 

[Dave Peterson]

Check your other post, too.

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.

 

[AndrewCerritos]

Hi, my take on selecting the range is:

activesheet.cells(day(date),2).resize(1,8).select

AC

 

[Dave Peterson]

That was very close to my take in the other post, too.