I have a shared workbook with protected sheets.
I want to place the current date in cell V when cell F is filled in. I want to make it so that all the cells in Column V are locked, so that they users can't alter the date. The date should only be populated the first time information is entered into column F.
I am using macro code to populate column V. When I have column V locked and something is entered in column F then I get the error message
"Runtime error '1004'. The cell of chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart ..."
I can't use interfaceonly:=true when protecting the sheet as it doesn't stay turned on when the file is reopened.
As the worksheet is shared I can't unprotect and re-protect the sheet, or unlock and relock the cells.
The code I am using to enter the dates with is below.
Does anyone have any other suggestions about how I could acheive what I want to do.
I want to place the current date in cell V when cell F is filled in. I want to make it so that all the cells in Column V are locked, so that they users can't alter the date. The date should only be populated the first time information is entered into column F.
I am using macro code to populate column V. When I have column V locked and something is entered in column F then I get the error message
"Runtime error '1004'. The cell of chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart ..."
I can't use interfaceonly:=true when protecting the sheet as it doesn't stay turned on when the file is reopened.
As the worksheet is shared I can't unprotect and re-protect the sheet, or unlock and relock the cells.
The code I am using to enter the dates with is below.
Does anyone have any other suggestions about how I could acheive what I want to do.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
If Target.Offset(0, 16).Resize(1, 1).Formula = "" Then
Application.EnableEvents = False
Target.Offset(0, 16) = Date
Application.EnableEvents = True
End If
End If
End Sub