Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBA - Select a range based on a date
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 - Select a range based on a date
The code below would unlock Range B3:I3 (as of today) on sheet 1.
The cells property is similar to the Address function except it is not a function. Note the dots before Range and Cells. These tie the references to the sh variable which represents Sheets(1). If these dots are omitted and another sheet is active, the code will fail. Details for this code can be found in the VBA help file. To access that file, press Alt + F11 to open the VBE and click the question mark in the circle on the menu bar. Then type in the key words in the search box to bring up the topic menu. Good luck. Sub dued() Dim s As Range, sh As Worksheet Set sh = Sheets(1) With sh Set s = .Range(.Cells(Day(Date), 2), .Cells(Day(Date), 9)) End With sh.s.Locked = False End Sub " 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select the first cell of the last row in a range - Excel 2003 | New Users to Excel | |||
Excel 2003 VBA Range selection based on system date | Excel Discussion (Misc queries) | |||
Sum select cells based on date range | Excel Worksheet Functions | |||
Excel 2003 won't let me select my own regression output range. | Excel Discussion (Misc queries) | |||
Select cell from range based on input in excel xp | Excel Discussion (Misc queries) |