Help With Protecting Date Based Ranges

DumbMiner

New Member
Joined
Aug 8, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi there, long time reader, first time poster.

I am working on a production KPI table and I want to be able to lock all data entered prior to today's date and I would like this protection to be enacted upon saving. I found the code beow and adapted it for my purposes and the dates are in column D (4) of the sheet, there are blank cells in the column as I have left spaces between the different shifts/days. I had it working with a previous iteration when I had it run with changes to the sheet but it was too onerous as it would run after every entry and it was overkill. This code now doesn't run when I use the save button, but if I use the "Protect Sheet" button then it locks the range that I want it too... in this sheet. When I use the same code on different sheets in the workbook or other workbooks it does not work correctly. I would love any suggestions from you geniuses.

Thank you!


[/CODE]
Sub Worksheet_BeforeSave(ByVal Target As Excel.Range)
'Code to lock previous days data entered

Dim xRow As Long
xRow = 3
ThisWorkbook.ActiveSheet.Unprotect Password:="Harte"
ThisWorkbook.ActiveSheet.Cells.Locked = False
Do Until xRow = 10000
If Cells(xRow, 4) < Date Then
Rows(xRow).Locked = True
End If
xRow = xRow + 1
Loop
ThisWorkbook.ActiveSheet.Protect Password:="Harte"


End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,144,568
Messages
5,725,038
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top