Select a range based on system 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.


0
tewsuave (1)
12/3/2008 10:28:33 PM
excel 39879 articles. 2 followers. Follow

3 Replies
598 Views

Similar Articles

[PageSpeed] 32

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 - gsnu2007k


"tewsuave@gmail.com" 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.
> 
> 
> 
0
GarysStudent (1572)
12/4/2008 1:22:00 AM
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.

tewsuave@gmail.com 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
0
petersod (12005)
12/4/2008 2:06:38 AM
I think this might be a simpler way to code it...

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

--=20
Rick (MVP - Excel)


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in =
message news:7A833B20-FF3F-48D3-8841-56D237FA0069@microsoft.com...
> Sub UnlockUm()
> Dim s1 As String
> Dim s2 As String
> s1 =3D Evaluate("ADDRESS(DAY(NOW()),2)")
> s2 =3D Evaluate("ADDRESS(DAY(NOW()),9)")
> Range(s1 & ":" & s2).Locked =3D False
> End Sub
>=20
> --=20
> Gary''s Student - gsnu2007k
>=20
>=20
> "tewsuave@gmail.com" wrote:
>=20
>> Part 1
>>=20
>> 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.
>>=20
>> For example if today is 11/5/08 then we want to unlock row 5,
>> specifically cells B5:I5.
>>=20
>> 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.
>>=20
>> Please note the following function examples that return the start and
>> ending cells of the desired range.
>>=20
>> =3DADDRESS(DAY(NOW()),2) this will display the start cell of the =
range
>> (B5) and=3DADDRESS(DAY(NOW()),9) will display the last cell in the =
range
>> (I5).
>>=20
>> If anyone could assist with the writing of a subroutine that would
>> select the whole range, for example;
>>=20
>> ADDRESS(DAY(NOW()),2).ADDRESS(DAY(NOW()),9).Select
>>=20
>> That would be the first step to my solution.
>>=20
>> Part 2
>>=20
>> Tying it all together...
>>=20
>> 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
>>=20
>> So we can allow specific users based on their Active Directory =
Account
>> to edit a particular range.
>>=20
>> However, at a minimum, opening the sheet unlocked (protected) and
>> saving it locked (protected) would work.
>>=20
>> Thanks in advance for staying up at night working on this.
>>=20
>>=20
>>
0
12/4/2008 2:54:49 AM
Reply:

Similar Artilces:

Receiving system error in Excell 2007
A client is receiving this error "system Error &H80004005(-2147467259)" in excel 2007 when opening a new file or an exisiting file. This error came up after visio 2003 was uninstalled and visio 2007 got installed on his system. This error can be a lot, and I looked a bit around where they say it might be something with memory. But then I wonder why he never gave this error before untill visio 2007 got installed. Any help is welcome and I would be very thankfull to those who can help me in this issue. Greetz Hi, Take a look at the following article: http://support.micr...

Intel I7 4 core .. why showing 8 cpus in system ?
Intel I7 4 core .. why showing 8 cpus in system ? Odd .. now using a Intel i7 quad core cpu . but the performance tab is showing 8 cpu history plots and the device manager is listing 8 cpu's ? is this anything to do with 32 / 64 bit ? G .. It's called Hyper-Threading technology. The quad core will show 8 threads available to the operating system which means better performance. -- The Real Truth http://pcbutts1-therealtruth.blogspot.com/ *WARNING* Do NOT follow any advice given by the people listed below. They do NOT have the expertise or knowled...

Outlook Express works, Outlook doesn't on only one XP system
I have two XP client systems and my own Linux mail server. The XP Pro system can use Outlook to access mail. But the XP Home system can use Outlook Express but not Outlook, although setup seems the same. When sending a test message in Outlook, it finds the server, but both send and receive fail, saying the server didn't respond (check ports and SSL). Not using SSL, and the ports are standard. What's more, Outlook Express on the same client connects and works fine. I've checked the DNS, the routing, ping, firewall, etc. (XP firewall disabled, Linux firewall open to LAN.) What's ...

ticket system in 2003?
Is there a ticket system in Exchange 2003 like lotus Notes? I thought that I read that there was a ticket system for help desk activites. ...

Data Entry to a Cell Range
Can I set up a data entry form, so if every time I enter a value in a cell, it updates the next empty cell in a range? Thanks Lets assume that the form is used to update cell A1. We require that everytime A1 is updated the new value will be recorded in column B. Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Dim A1 As Range, t As Range Set A1 = Range("A1") Set t = Target If Intersect(A1, t) Is Nothing Then Exit Sub Application.EnableEvents = False n = Cells(Rows.Count, "B").End(xlUp).Row...

ActiveSync 3.7 doesn't stay up to date with Outlook 2003
I just recently upgraded to Outlook 2003 (from XP) and I've found that my Pocket PC won't stay current with emails while it's connected to my PC in the cradle. I've noticed the problem when I delete emails from my inbox on the PC or I move emails to a folder on the PC. I would expect ActiveSync to fire up and update the Pocket PC but it doesn't seem to recognize that anything has changed. However, if I disconnect the Pocket PC from the cradle and reconnect it seems to recognize the differences and get itself up to date. Has anyone seen this problem? Any ideas o...

Dynamic Range Charts Across Worksheets?
Hi, I'm trying to create a graph of a dynamic range of cells. In order t do this, I defined two named functions (X and Y) to graph. Everythin works great, except that I need to do this for a lot of different set of data on different worksheets. The parameters for each worksheet ar the same, but I'm trying to find a way to get around defining a ne named function for every single worksheet. Is there some way to set the named function to refer to the workshee that the graph is located in, instead of a specific worksheet? Thanks -- Message posted from http://www.ExcelForum.com A few ...

Selecting a Specific Record Form from a subform
I have a form with a subform that I want to select a field from and have it open another form with the data selected from the sub form. I was able to get the operation to work in the subform using the following: Under Parameters of the first field of the subform -- Open4Edit_StationInfo Open4Edit_StationInfo used the following command: FindRecord Find What: =[Forms]![Station Info Subform]![Call Sign] Match: Whole Field Match Case: No Search: All Search As Formatted: Yes Only Current Field: No Find First: Yes The above works when I run the form "Station Info...

word freezes at the template selection screen at startup
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC My whole machine went down about a month ago. Finally got to installing office today and word freezes. I've uninstalled and reinstalled to no effect. Download and install the latest Office 2004 updates, which are the Office 2004 11.5.0 & 11.51 updates. In Office 2004, use Microsoft AutoUpdate to get the latest updates. From any Office application, go to Help > Check for Updates (or manually launch Microsoft AutoUpdate from the Applications folder). If any updates are needed, they will display in a sheet...

Select tab to import from excel spreadsheet in Macro
I have one excel file that contains multiple tabs. Is it possible to select a specific tab to import in a Macro? I have static labels on these tabs that can be referenced. If so, which function would that be? Your help is appreciated! Kanley Just to clarify, I just need to import one tab at a time, but when i specify the worksheet name in the range field as WORKSHEET!, it wouldn't take it. Please help! On Oct 30, 11:29 am, KT <kan...@rocketmail.com> wrote: > I have one excel file that contains multiple tabs. Is it possible to > select a specific tab to import in a Mac...

Notification Email on duration date
Hi I have a question that is there any way to send email to owner when their task is reaching due date set for the task. I tried to make it work with workflow but I couldn't. Also I read through all the post here and couldn't find anything relating to my question. Sorry, subject should be "Notification Email on due date", note "duration date" Thks, motoC "motoC" wrote: > Hi > > I have a question that is there any way to send email to owner when their > task is reaching due date set for the task. > > I tried to make it work with...

how to create a field based on many different conditions
Hi, I'm trying to create a field based on many different conditions in Make Table Query in Access 2003, e.g., if VAR1 and VAR2 meet certain condition, assign a value "A" to the new field "Category"; if VAR1 and VAR2 meet other condition, assign a value "B" to "Category" so on and so forth. I keep getting separate field for each condition, something like "Expr1023". Please help! Thank you! Since you haven't provided the expression or SQL statement, I will assume it is quite complex. I would remove the complexity from the query ...

Date Stamp
I've used this Macro and need to tweak it: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("a10"), .Cells) Is Nothing Then Application.EnableEvents = False With Me.Range("b10") .NumberFormat = "dd mmm yyyy" .Value = Now End With Application.EnableEvents = True End If End With End Sub This macro stamps B10 if something is entered in A10 but I also want this t...

How do I delete a selection w/o having to confirm my deletion?
I keep getting a "Delete Yes/No" prompt on my task bar after highlighting text and then hitting the delete button. I then have to hit the Y key to tell it yes, I do want this deleted. It's an irritating extra step. See http://word.mvps.org/FAQs/AppErrors/UnableToDeleteText.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "ChiTown Reenie" <ChiTown Reenie@discussions.microsoft.com> wrote in message news:8551E9A4-251E-4E65-A646-B8389DFB956F@microsoft.com... >I keep getting a "Del...

Selecting Cells with Shift Key
When I am selecting cells in Excell, I use the shift key and the arrows on my keyboard. If I pause at all, the selection is lost when I continue with the selection. Why? Can anyone help me? If you have removed your finger from the shift key then Windows will think you are starting a new selection. Just be sure that you don't touch the arrows without the shift being held down. LWhite "Jaymndad" <Jaymndad@discussions.microsoft.com> wrote in message news:961E850D-AFAD-444C-AB81-F4A609BCB3BA@microsoft.com... > When I am selecting cells in Excell, I use the shift key...

Date calcutation
Good afternoon, i need some help with the following: I have a maintenace DB, i have the following fields MachineHours (long int), MaintenaceHours (long int), MaintenaceDate (date) and WeeklyHourWork (long int). I have a text box NextMaintenace (date) where i calcute the date of the next maintenace based on the date of the maintenace (MaintenaceDate) difference between MachineHours, MaintenaceHours and WeeklyHourWork. example: if MachineHours=4000, MaintenaceHours=7000, MaintenaceDate=26-10-2007, WeeklyHourWork=40 then NextMaintenace=15-03-2009 This works fine but I need to put a limit to...

contact your system administrator
We often get the message that we cannot log into crm anymore and we need to contact the system administrator. The solution is to log off and log on again and the message is gone. This especially happens when we leave our pc 's on during the night. But why and what can we do to prevent this strange behavior? Thanks. -- John ...

Question about System.Xml.XmlDocument.SelectNodes
Hello group, I have a some xml that looks like this below: <tuneRequests ct="3" xmlns:sql="urn:schemas-microsoft-com:xml-sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <tr xsi:type="trGenericType" number="2"> <s name="KATU" call_sign="KATU" affiliation="ABC Affiliate" id="28455700" ppv="0" /> </tr> <tr xsi:type="trGenericType" number="3"> <s name="KOAB" call_sign="KOAB" affiliation="PB...

date in a text cell
I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have a regular date format in a text cell. Thanks If you format the cell as Text, then you'll have to type in what you want--exactly the way you want to see it. If you don't format it as text, you could type the date in anyway that's a date, then use a custom format of: dd-mmm-yyyy (format|cells|number tab|custom category) Dajana wrote: > > I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have > a regular date format in a text cell. > > Thanks -- Dave Peterso...

Drawing a selection box using CRectTracker
Hello, I have an MFC SDI CView application and I want to draw an selection box when the user left clicks and move the mouse. I have found the CRectTracker class but I can't get it to work I have this code: void CSCMLaserView2::OnLButtonDown(UINT nFlags, CPoint point) { SetCapture(); m_startPt = point; m_theRectTracker.TrackRubberBand(this,point,TRUE); } void CSCMLaserView2::OnLButtonUp(UINT nFlags, CPoint point) { //Release the capture on the mouse ReleaseCapture(); CSCMLaserDoc* pDoc = GetDocument(); ASSERT(pDoc != NULL); CClientDC dc(this); OnPrepareDC(&dc); // Co...

SQL Query to transform/group data by Date
Hi, I have a large Access table with data organised as follows: Field1: Code Field2: Date Field3: Value1 Field4: Value2 There are seveal different codes and therefore duplicate dates. I'd like to run a query to bring back each code grouped by date and so put the codes along the top as feilds. For example the query below brings back the following data for two codes. SELECT field2, field1, field3 FROM Data WHERE field1 In ('LLOY','RSA'); 21/01/05, LLOY, 12454 22/01/05, LLOY, 31541 21/01/05, RSA, 21241 22/01/05, RSA, 12414 Instead I want the data to look like this: ...

Ranking system from names and scores
Thanks to Ron and AlfD, I have my new spreadsheet half done. This will make it complete, although I won't matter a huge amount if n one can work it out. I just like to keep your brains in tipto condition :D Ok, here we go. Cell D2 contain someones name. F20 contains this persons overall score. also we have G2=name and final score in I20 so the names are in series, D, G, J, M, P, S and V (2) The scores are in F, I, L, O, R, U and X (20) in Cells A26 is "1st", A27 is "2nd" - to A32 which is "7th" B26 to B32 will contain a name. C26 to C32 will contain a s...

CONVERT TIME & TRIM DATE
I need help converting time to an AM/PM time format our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. The date displays: 2007-03-09 00:00:00.000, how do I have it display the date as 03-09-2007? Thanks. Specify the display format of the control or field: mm-dd-yyyy RENEE705 wrote: >I need help converting time to an AM/PM time format >our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am >finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. >The date displays: 2007-03-09 00:00:00.0...

Not enough system resources to display completely
I am getting this message box more frequently in my Excel workbook. Cannot find in help topics. Thanks Check out the below..(Might help) http://www.contextures.com/xlfaqapp.html#xlb -- Jacob (MVP - Excel) "bgkgmg" wrote: > I am getting this message box more frequently in my Excel workbook. Cannot > find in help topics. > > Thanks I checked out site and is very helpful but why does it not happen when I adjust the zoom percentage? "Jacob Skaria" wrote: > Check out the below..(Might help) > http://www.contextures.com/xlf...

OWA session Timeout (Form Based Auth )
Wondering if it is possible in Exchange 2003 SP2 to have different OWA timeout for different users using the same OWA HTTP VS to connect... If not, can a second HTTP VS on the same server as the first have different timeout, or is it a server setting ( I think it is done trough OWA web admin tool ) !!! Thanks !!! ...