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

Mr Davo · #1 10-17-2012, 10:40 PM

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 · #2 10-29-2012, 01:07 AM

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

Code:

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Long, 3 Column Table - Can I make Fit Into Page Columns?	Rigwald	Word Tables	9	08-07-2012 08:14 PM
Is there a way to automatically highlight the column and the row that of the current	Jamal NUMAN	Excel	8	02-14-2012 02:58 PM
Make row in one sheet equal column of another	hypertyper	Excel	6	01-31-2012 03:48 PM
Can I change the horizontal scrollbar to scroll smoothly rather than column by column	carpat	Excel	0	01-10-2012 09:34 AM
Need to search a column for a macth and return a result from a third column	pdfaust	Excel	2	02-03-2011 03:02 PM