Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBA Assistance needed
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBA Assistance needed
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 -- Gary''s Student - gsnu200817 "Tymothé" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBA Assistance needed
On 3 Dec, 22:29, Tymothé wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBA Assistance needed
Check your other post, too.
Tymothé wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBA Assistance needed
Hi, my take on selecting the range is:
activesheet.cells(day(date),2).resize(1,8).select AC "Dave Peterson" wrote: Check your other post, too. Tymothé wrote: 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. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBA Assistance needed
That was very close to my take in the other post, too.
AndrewCerritos wrote: Hi, my take on selecting the range is: activesheet.cells(day(date),2).resize(1,8).select AC "Dave Peterson" wrote: Check your other post, too. Tymothé wrote: 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. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup assistance needed | Excel Worksheet Functions | |||
CODE Assistance needed PLEASE please please | Excel Discussion (Misc queries) | |||
Code assistance needed | Excel Programming | |||
VBA Assistance Needed | Excel Discussion (Misc queries) | |||
Macro assistance needed | Excel Programming |