Code for a timestamp.

**Zirus** · 05-11-2007, 07:53 AM

Hello, im new to the forum, so excuss my ignorance if I make any typos. I am creating a Excel worksheet and need a code to allow a one time timestamp. For example: If I want B1 to display the time that A1's value goes to anything above 1, I put: If(A1>0,NOW(),""). The only thing wrong with that equation is that the time changes everytime something is changed on the sheet. I need the time to stay once its been put in there. I have been told that this may not be possible with Excel, but thats why im on the forum now. If anyone can help that would be greatly appretiated.

**sweep** · 05-11-2007, 08:52 AM

Hi,

This need to go in the VB editor.

Press ALT & F11 to open the VB editor
In the pane on the left, double click the worksheet you need to work on
A dropdown menu will appear that is defaulted to General - change it to worksheet and the following should appear:-

Please Login or Register  to view this content.

Copy and paste so that it looks like this:

Please Login or Register  to view this content.

You can change this to suit - but at the moment it looks at cell B1, and as soon as it has a value greater than 1, puts the time and date into cell A1

**Zirus** · 05-11-2007, 01:40 PM

Dude that worked great, thanks man. So in order to do it for a whole column I just do something like.

Private Sub Worksheet_Activate()
Dim test As Variant
Static runtest As Variant

Please Login or Register  to view this content.

I just tried that and it widnt work, how might I word it to make it do an entire column.

**mudraker** · 05-11-2007, 07:27 PM

Zirus

Welcome to excelforum

Please read forum rules & wrap your VBA code
See blue link below for forum rules
See red link below for instructions on wrapping your code

**Zirus** · 05-14-2007, 10:07 AM

Ok I wrapped the code like the instructions said. It still works the same way, and I believe I followed the rules as described. If I have not please let me know so that I may correct the problem in the future.

here is the code that I have so far.

Please Login or Register  to view this content.

Is there a way(other than repeating the code) to make the process work from "C10" to "C110"? Again sry for the misshap on the posting rules.

**mudraker** · 05-15-2007, 02:43 AM

Zirus

In your previous post your VBA code is wrapped correctly - thank you

Now to your problem

I am supplying 2 macros
Macro 1 is if c10 to c110 are values - not formulas
Macro 2 is if c10 to c110 are formulas

Place one of the macros on same sheet as your worksheet change macro

Macro 1

Please Login or Register  to view this content.

Macro 2

Please Login or Register  to view this content.

**Zirus** · 05-15-2007, 01:10 PM

Great that seems to work just how that I wanted it to. I appreciate everything.

Code for a timestamp.

LinkBack

Thread Tools

Rate This Thread

Display

Code for a timestamp.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions