Forum Discussion

VinScho's avatar
VinScho
Copper Contributor
Jan 22, 2022

Adding a further level of restriction to date record user input password protected cells

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  

excel

1 Reply

  • bosinander's avatar
    bosinander
    Iron Contributor
    Jan 22, 2022

    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.

Resources