Auto population of current date when data entered.

[haumoana]

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.

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

 

[BrianB]

interfaceonly:=true when protecting the sheet as it doesn't stay turned on when the file is reopened

can put this in he WorkBook_Open event.

 

[haumoana]

Hi

I tried putting the protection code in the workbook open event, but again get the 1004 runtime error when opening the workbook when it is shared.

The protection works fine if the workbook is not shared, but due to the nature of the use of the file it is not possible to unshare the workbook.

Private Sub workbook_open()
Sheets("Tracker").Activate
      
ActiveSheet.Protect userinterfaceonly:=True
End Sub

Thanks

 

[Andrew Poulsom]

Couldn't you unlock the cells in column V and hide the column? The users won't be able to unhide the column if the worksheet is protected.

 

[haumoana]

Thank you eveyone for you help.

I have solved this problem in a different way.

I have used the on selection event to get the value of the cell.
Then if someone tries to alter the date whatever they enter is overwritten with the value held from the selection event.

There are was for someone to still alter the date, but I can live with the limitatations, as they are the same as for data validation.

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
    
    If Target.Column = 22 Then
        Application.EnableEvents = False
        Target.Value = vOldVal
        Application.EnableEvents = True
    End If
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    vOldVal = Target.Value2
End Sub