Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to protect cells based on date in Excel?

Normally, we can protect the worksheet to prevent others editing or modifying the cell values, but, sometimes, you need to protect the cells based on date. For example, I want to protect other cells but only allow the cell values of today’s date to be modified as following screenshot shown, this article will talk about how to protect cells based on date.

doc protect by date 1

Protect all rows except today’s date row with VBA code

Protect all rows the date has passed with VBA code


arrow blue right bubble Protect all rows except today’s date row with VBA code

Only allow the row which equal today’s date to be modified, the following code can help you, please do as this:

1. Right click the sheet tab that you want to protect the cells based on date, and then choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module:

VBA code: Protect all rows except today’s date row:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161025
    If Range("E" & Selection.Row).Value <> Date Then
        ActiveSheet.Protect Password:="111111"
        MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
    ElseIf Range("E" & Selection.Row).Value = Date Then
        ActiveSheet.Unprotect Password:="111111"
        ActiveSheet.EnableSelection = xlNoRestrictions
    End If
End Sub

doc protect by date 2

Note: In the above code, the letter E is the column header where the date located, “111111” is the password to protect this sheet. You can change them to your need.

2. Then save and close this code window.

(1.) If you click other cells beyond today’s date row, an prompt box will pop out to remind you the cell can’t be edited, see screenshot:

doc protect by date 3

(2.) If you click and edit the row equals today’s date, it will be modified successfully, see screenshot:

doc protect by date 4


arrow blue right bubble Protect all rows the date has passed with VBA code

If you need to protect all rows the date has passed, only allow today and future date rows to be modified, please apply the following VBA code:

1. Right click the sheet tab that you want to protect the cells based on date, and then choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module:

VBA code: Protect all rows date has passed:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice 20161025
  Dim xRow As Long
  xRow = 2
  ThisWorkbook.ActiveSheet.Unprotect Password:="111111"
  ThisWorkbook.ActiveSheet.Cells.Locked = False
  Do Until IsEmpty(Cells(xRow, 5))
    If Cells(xRow, 5) < Date Then
      Rows(xRow).Locked = True
    End If
    xRow = xRow + 1
  Loop
  ThisWorkbook.ActiveSheet.Protect Password:="111111"
End Sub

doc protect by date 5

Note: In the above code, the number 5 is the column number where the date located, “111111” is the password to protect this sheet. You can change them to your need.

3. Then save and close this code window.

(1.) If you click the cells date has passed, an prompt box will pop out to remind you the cell can’t be edited, see screenshot:

doc protect by date 6

(2.) If you click the cell of rows to try to change the values in today’s date or future date, it will be modified successfully, see screenshot:

doc protect by date 7


Recommended Productivity Tools for Excel

kte tab 201905

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Niall · 5 months ago
    As someone who is relatively new to VBA, would you mind explaining why xRow = 2 in Line 4?



    Thanks
    • To post as a guest, your comment is unpublished.
      Piotr · 5 months ago
      because in row no. 1 you have header
  • To post as a guest, your comment is unpublished.
    Bobby · 1 years ago
    This works great. but how can I switch it to unlock a column based on dates in row 3?
  • To post as a guest, your comment is unpublished.
    TAHER KANPURWALA · 1 years ago
    Hello.....


    I am using the code for Protect all rows except today’s date row........



    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Updateby Extendoffice 20161025
    If Range("A" & Selection.Row).Value <> Date Then
    ActiveSheet.Protect Password:="111111"
    MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
    ElseIf Range("E" & Selection.Row).Value = Date Then
    ActiveSheet.Unprotect Password:="111111"
    ActiveSheet.EnableSelection = xlNoRestrictions
    End If
    End Sub


    After i save i and click on cell i get message only today's date row can be edited....but if i double click the cell the i can edit...pls help
  • To post as a guest, your comment is unpublished.
    LUIS GARCIA · 1 years ago
    hi is it possible to lock certain cells depending on the date that is directly above them in a column?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Lusis,
      Can you give your problem more specifically? Or you can attach a screenshot as your problem.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Lmg · 1 years ago
    Hi is it possible to lock columns by the date directly above the columns?