Adding timestamp

You may like to install the procedure below. It doesn't prevent the entry like a worksheet protection does. Instead, it reverses the modification with a notice to the user. So, the cell doesn't get changed after all.

Private Sub Worksheet_Change(ByVal Target As Range)

    ' specify one or several contiguous columns
    ' where no change is allowed
    Const ProtectedClms As String = "A:C"

    If Not Application.Intersect(Target, Range(ProtectedClms)) Is Nothing Then
        MsgBox "Cells in range """ & ProtectedClms & """ may not be modified." & vbCr & _
               "Your change will be rolled back now.", _
               vbInformation, "Input restriction notice"
            ' prevent the Uno action from calling this procedure
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
    End If
End Sub

It's an event procedure. It responds to the "Change" event, meaning it fires when a change is made in the worksheet. Which worksheet? That is specified by the code's location. It works on the worksheet in whose code module it is deposited. That is one of the code modules VBA sets up for you. You find them listed, by sheet name, at the top of the Project Explorer pane. Double-click on the correct one to open it and paste the code there.

It may happen that you do want to make a change in one of the protected columns. If so, you have to disable the event procedure. You might simply disable the code, by remming it all out, or its crucial lines, for the duration of the repairs. Another way is to enter Application.EnableEvents = False in the Immediate window (or write a little sub to contain only that line), in fact, as shown in practise in the above procedure. You can re-enable events by running Application.EnableEvents = True from the Immediate window or from code or by restarting Excel.

It just occurs to me that the code that writes the time stamp could also trigger this procedure. If so you need to disable events while the code is doing that.

Application.EnableEvents = False
' Write to the sheet here
Application.EnableEvents = True

If the time stamping itself is done using an event provedure the two procedures have to be combined. You can do that using a simple IF statement.

If Not Application.Intersect([.. is this ...]) Then
    ' add the time stamp
ElseIf Not Application.Intersect([... is that ...]) Then
    ' prevent it from being corrupted
End If

Try this:

1. Select all cells in the worksheet and right-click > Format Cells > Protection tab > uncheck Locked.
2. Select the timestamp column and right-click > Format Cells > Protection tab > check Locked.
3. In the macro, unprotect before chaning it and protect after changing it.

You can use a password for the protection if you want and then also password protect the project and no one will be able to see the password in the macro.