Datestamp an adjacent cell & not allow later alteration

[Glio]

I have been using the following code for some time

Please Login or Register  to view this content.

& it works very well, but if I lock the offset column it doesn't.

I also want the offset cell not to be changed if the Target.Column is re-edited. Is this possible?

[royUK]

Try this

Please Login or Register  to view this content.

[Glio]

Roy,
Thanks very much for your very prompt response!

I copied your code into Module1, Sheet1 and ThisWorkbook and could not get the code to work.
I have tried changing the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

but this does not work either.

Have I done something wrong?

Regards

Geoff

[royUK]

You need to put the code in the worksheet module, not ThisWorkBook or a standard module. Right lick the sheet tab and select View code. This will open the VB Editor. In the left hand drop down select WorkSheet. Then paste the code.

[Glio]

Roy,

I now understand that your code is checking to see if the the 3rd cell to the right of the data input cell (in column 3) is protected, if it is, it unprotects it and is then meant to insert the date and time into that cell, before protecting it again, even if the spreadsheet is protected by password. For some reason the Date & Time is not being entered. I tried to change the Value to Now() but that doesn't work either.

I did put your code into a Module to begin with, but when it didn't work there I tried it in the other Objects.

Regards

Geoff

[royUK]

Because the default status of all cells in Excel is Locked you must first of all make sure that the date stamp cells are not Locked. Select all cells that you want the date stamp in, in this example column F, then Format menu >-Cells>-Protection and uncheck the Locked option.

[Glio]

Roy,

Excellent that works well.
The spreadsheet I am working on has some names in column B, which I want to protect completely. If I protect the worksheet with a password, and enter data in column C, the code demands the password before entering the date time in column F.

Is it possible to do this by altering your code?

Regards

Geoff

[royUK]

Try this amended code, remember to put your password where indicated

Please Login or Register  to view this content.

[Glio]

Roy,

Many, many thanks.


Geoff