Jan 22 2022 04:22 AM
Hello,
I have an issue where I have ranges of password protected cells on a locked sheet (input by various users with individual passwords).
The users input a date in each cell to confirm completion of a task and I am looking to automate the removal of their password access 2-3 days after they have inputted the date value into the cell so it remains a protected document that cannot be further amended at any later date without admin access.
I realise this is probably not available as an option but I heard that people on this forum were extremely knowledgeable.
Thanks
Jan 22 2022 10:13 AM
@VinScho Hi,
You will have to use a macro and this may be a step on the way.
I have unlocked cells formatted as Home: Cell Styles: Input.
Sub protectAfterLimit()
Dim cell As Range
ActiveSheet.Unprotect ("prettylongpassword")
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Locked = False Then
' cell.Select
If cell.Value = "" Then
ElseIf cell.Value <= Now() - Range("limit_days").Value Then
cell.Locked = True
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End If
Next cell
ActiveSheet.Protect "prettylongpassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
The attached file is saved without protection.