Automatically enter date into a column and make that column read only

[Mr Davo]

Hi Everyone,

I have a date column in my spreadsheet and there are several actions that I would like to apply to it -

1). Auto populate the date column with the current date only when the column to the left has data entered into it (please see below)



2). Make the date column read only so that it cannot be modified by other parties.

Any help will be greatly appreciated.

Kind Regards,

Davo

[macropod]

You can do this with a 'Worksheet_Change' macro in the relevant worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Range("B:B")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
If IsDate(Target.Value) Then Target.Locked = True
ActiveSheet.Protect
End Sub

To use the macro, first format all cells as neither locked nor hidden. Then apply worksheet protection. The macro above assumes no protection password.

To install the macro, press Alt-F11 to open the vb editor, then select and open the relevant worksheet module from the panel on the left-hand side of the screen. Copy & paste the code to that module, then press Alt-F11 again to return to the workbook. Note that dates only be protected - other content will not be protected.