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.
Protect all rows except today’s date row with VBA code
Protect all rows the date has passed with VBA code
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
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:
(2.) If you click and edit the row equals today’s date, it will be modified successfully, see screenshot:
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
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:
(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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, 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!
