Excel protect function

New Contributor
Hello, my name is Milos, I'm new on this site and need yoor help.
In one excel sheet I wont to protect column of the end on today but all next days must be unprotect.
4 Replies


It's a bit difficult to visualize what you want. Could you attach a small sample workbook to illustrate your problem?

I want to automaticly protect all columns when date is end.
best response confirmed by allyreckerman (Microsoft)


Step 1:

Select all cells that the user should be able to edit in principle.

Press Ctrl+1 to activate the Format Cells dialog.

Activate the Protection tab.

Clear the Locked check box.

Click OK.


Step 2:

Press Alt+F11 to activate the Visual Basic Editor.

In the Project Explorer pane on the left, double-click ThisWorkbook under Microsoft Excel Objects.

Copy the following code into the module:

Private Sub Workbook_Open()
    Dim c As Long
    Dim m As Long
    With Worksheets(1) ' or Worksheets("NameOfSheet")
        .Protect Password:="Secret", UserInterFaceOnly:=True
        m = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For c = 2 To m
            If .Cells(1, c).Value >= Date Then Exit For
            .Cells(1, c).EntireColumn.Locked = True
        Next c
    End With
End Sub

Step 3:

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.




Good, thank you so much!!