Protect Past Data from editing (<= TODAY)

[twinklestar23]

Excel Experts,

I need your help in creating a lock for past data for my Inventory List in Excel 2007.
Ideally, I'd like to record macro that will select a list of data that were input before today, and lock these cells.

I know selective cells locked can be done by doing this;
Lock or unlock specific areas of a protected worksheet - Excel

HOWEVER, how do i modify the macro to only select data with dates before TODAY and then lock these cells?

i.e:
Date | Item | Quantity
1 July 2013 | Pencil | 5
<Todays date> | Eraser | 5

The data on 1 July 2013 will be locked and can't be changed once i hit the button/macro.

I'm sure it can be done, but i just can't link the dots.
Please help!!

 

[mumps]

First of all, unlock all the cells that will contain data and then protect the worksheet. Assuming the date is in column A, try:

Sub LockCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("A2:A" & LastRow)
        If rng < Date Then
            Rows(rng.Row).EntireRow.Locked = True
        End If
    Next rng
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

 

[twinklestar23]

Hi Mumps,

THANK YOU so much for your reply.
How does your code work? So i have to click this button before entering the data for today?
Also, can I add a password when protecting the data ( at the last ActiveSheet.Protect code)? So someone will be able to unprotect the past locked data, but only with a password.

Many thanks again!!!

First of all, unlock all the cells that will contain data and then protect the worksheet. Assuming the date is in column A, try:

Sub LockCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("A2:A" & LastRow)
        If rng < Date Then
            Rows(rng.Row).EntireRow.Locked = True
        End If
    Next rng
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Hi twinklestar. You begin by entering all your data in your spreadsheet with the date in column A starting at row 2. Row 1 usually contains the column headers. To lock the appropriate rows, you have to run the macro. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. I've modified the macro to add a password. Just change "password" in the code to whatever password you choose.

Sub LockCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="password"
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("A2:A" & LastRow)
        If rng < Date Then
            Rows(rng.Row).EntireRow.Locked = True
        End If
    Next rng
    ActiveSheet.Protect Password:="password"
    Application.ScreenUpdating = True
End Sub

 

[twinklestar23]

Hi Mumps,

Thanks again...
Just one thing, this code works just like i normally protect the whole worksheet after i updated it. But it doesn't lock ONLY the past data (data that were entered today). If i need to un protect it, i need to go to Review --> Un protect Sheet.
Not sure if I do this wrongly, because i can see that your code is finding today's date so it should work only for past data.
Maybe there's something i didn't do right?

Attached is how my spreadsheet looks like. I inserted your code in a 'Lock Cells' button.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date (Column A)
[/TD]
[TD]Item (Column B)
[/TD]
[/TR]
[TR]
[TD]1 July 2014
[/TD]
[TD]Spoon
[/TD]
[/TR]
[TR]
[TD]8 July 2014
[/TD]
[TD]Forks
[/TD]
[/TR]
</tbody>[/TABLE]

After i entered the latest data, I click the button and it locks the entire sheet with the password that I set. But my goal is just to lock past data (entered before today's date as in Column A).

Sorry for going back and forth.. I'm really2 grateful for your help!! Thanks a bunch!!

 

[mumps]

Hi twinklestar. Try the revised macro below. It will lock only the rows that have a date before today's date not the entire sheet. This will allow you to enter more data without having to unprotect the sheet. Keep in mind, however, that once cells are locked, if you want to modify those cells, you must first manually unprotect the sheet, make your modifications and then run the macro again.

Sub LockCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="password"
    ActiveSheet.Cells.Locked = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("A2:A" & LastRow)
        If rng < Date Then
            Rows(rng.Row).EntireRow.Locked = True
        End If
    Next rng
    ActiveSheet.Protect Password:="password"
    Application.ScreenUpdating = True
End Sub

 

[twinklestar23]

This works beautifully... Thank you so much!!

 

[mumps]

My pleasure.