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