automatic date

Posted by Bouko on September 06, 2001 11:40 AM

I'm looking for a way to achieve the following:
When entering data in an cel in column A I would like the current date and time to automatically appear in column B so the cell in column B will show the date/time I entered something in the cell column A (next to the cell in column B). I want the date/time to be a fixed date/time, I don't want it to change.
On entering something in a different cell in column A, in the cell next to that one (in column B) the current date/time of the input in column A should appear automatically as a fixed date/time.
This way it will be clear on what date/time I entered something in column A.
After saving the sheet, the next time I open it the dates/times will still have to be the same as they were the first time.
Can anyone help me with this?

Posted by Barrie Davidson on September 06, 2001 11:50 AM

Try this in the worksheet's code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Barrie Davidson
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

If you need any help just let me know.

Regards,
Barrie

Posted by Bouko on September 06, 2001 12:30 PM

Posted by Bouko on September 06, 2001 12:32 PM

Thanks Barrie.
I never did anything with codes in Excel before. Where should I enter the code you gave me?

Posted by Barrie Davidson on September 06, 2001 12:37 PM

Right click on the worksheet's tab and select "View Code". This will take you in to the Visual Basic Editor window. Paste this code in to the window (usually at the right of the screen). Then ALT+Q to exit the Visual Basic editor.

Hope this explanation is enough for you.

Regards,
BarrieBarrie Davidson

Posted by Bouko on September 06, 2001 12:43 PM

Thanks a lot Barrie

Thanks!

It works perfectly.

Kind regards,
Bouko

Posted by Bouko on September 06, 2001 1:14 PM

One more question Barrie

Is there any way to protect the cells the date appears in so the dates generated won't be able to be changed by hand?

Posted by Barrie Davidson on September 06, 2001 1:26 PM

Re: One more question Barrie

You bet. If you unlock the cells in column A and then protect your sheet (I used the password "buzz" when I was testing), this changed code will work for you.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
ActiveSheet.Unprotect ("buzz")
Target.Offset(0, 1).Value = Now()
ActiveSheet.Protect ("buzz")
End If
End Sub

Note, if your password is not "buzz", you'll have to change the code.

Regards,
BarrieBarrie Davidson

Posted by Bouko on September 06, 2001 1:31 PM

Thanks again

Thanks again!

regards,
Bouko