Cell restrictions by date

[ChoiceMC]

Hi Excel masters,

I want to restrict cell editing if the date is not "today".

Meaning - I have a table like this:



And I would like to restrict access to cells so they can only fill the cells of todays date (each date has 3 rows), so they can't edit yesterdays inputs, or make inputs ahead for tomorrow.

Can it be done?

Thanks

 

[Zubair Muhammad]

Hi ChoiceMC

Try this. Add the code below in the Worksheet Module of the sheet you want to protect.
Modify according to your needs

Private Sub Worksheet_Activate()
Dim mysheet As Worksheet, rangetolock1 As Range, rangetolock2 As Range
Set mysheet = ActiveSheet

On Error Resume Next
mysheet.Unprotect "excel"
Cells.Locked = True

For i = 1 To 100 'Specify the number of rows to search for today's date
    If Not Range("A" & i) = "" Then
            If DateValue(Range("A" & i)) = Date Then
            Debug.Print Range("A" & i).Address
                    'specify the cells to unlock
                    'Example 2 cells below
                Set rangetolock1 = Range(Range("A" & i).Offset(1), Range("A" & i).Offset(2))
                    'Example 3 columns and 3 rows to the right
                Set rangetolock2 = Range(Range("A" & i).Offset(, 1), Range("A" & i).Offset(2, 3))
                
                rangetolock1.Locked = False
                rangetolock2.Locked = False
                mysheet.Protect "excel"
                Exit Sub
            End If
    End If
Next i
End Sub

 

[Zubair Muhammad]

The above code runs everytime the sheet is activated and performs following steps



1) It unprotect the sheet first using password "excel"
2) It locks all the cells
3) It searches for Today's date in Column A from A1 to A100
4) if it finds it, it unlocks 2 cells beneath it and (3 Columns and 3 rows ) starting from the next cell in column B
5)It then protects the sheet using password "excel"


You can modify the code above according to your needs