Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

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?

Re: automatic date

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

Re: automatic date

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

Re: automatic date

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?

Re: automatic date

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

Thanks a lot Barrie

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

Thanks!

It works perfectly.

Kind regards,
Bouko

One more question Barrie

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

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?

Re: One more question Barrie

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

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

Thanks again

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

Thanks again!

regards,
Bouko

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.