anyway to make auto fill time value static?

**Tezzz** · 01-24-2009, 07:26 AM

I have 2 cell, A1 and B1. When any data is entered into A1, B1 will display the time corresponding to when the data was entered.

I was thinking about function for B1 would be "=IF(A1="","",now()) ". It worked such that it will display the time, however the value in B1 will automatically update itself if I were to randomly input any data in any other cell too.

For example, I input "testing" in A1. B1 display "11:20PM". Ten minutes later, I input "hello" in A2. B1 now display "11:30PM".

Anyone know a way to stop B1 from updating? or only update when new data is entered into A1 only? Thanks.

**VBA Noob** · 01-24-2009, 07:31 AM

Maybe a event macro.

Right click sheet tab > select view code > paste in the below

Please Login or Register  to view this content.

VBA Noob

**Tezzz** · 01-24-2009, 07:41 AM

It didn't seem to work.
But thank tho.

**VBA Noob** · 01-24-2009, 07:45 AM

Should work unless it's a formula in A1

See example

VBA Noob

**Tezzz** · 01-24-2009, 07:57 AM

Ah it did work. Thank a lot VBA!

**VBA Noob** · 01-24-2009, 08:02 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Tezzz** · 02-01-2009, 05:07 AM

VBA, I need help figuring out how to set this up for a range. Such as I name A1:A5 = "Time" and with the respective formula in each cell, how would i make each cell auto fill time static? The macro worked for 1 cell, but anyway to make it for a range? Thank.

**martindwilson** · 02-01-2009, 08:09 AM

another one is a udf

Please Login or Register  to view this content.

use as any function e.g.
=IF(OR((A1)=" ",A1=0),"",puttime())

this will hold the time unless a1 is cleared

**Tezzz** · 02-01-2009, 08:23 AM

martindwilson, not sure if that worked. I have attached a simple example. Can you fill in the formula and code so i can see how you do it? Thanks.

Basically, when any number is entered into "Cost" , its respective "time" will be displayed.

**martindwilson** · 02-01-2009, 08:38 AM

there you go formula allows for someone just clearing cell with space
i'd lock the cells with the formulas in though

**Tezzz** · 02-01-2009, 05:18 PM

Thank martindwilson.

**dwogn** · 11-27-2010, 02:03 PM

Originally Posted by Tezzz

VBA, I need help figuring out how to set this up for a range. Such as I name A1:A5 = "Time" and with the respective formula in each cell, how would i make each cell auto fill time static? The macro worked for 1 cell, but anyway to make it for a range? Thank.

I am in need of this same information. I tried martindwilson's code, but could not get it to work. Any more help for another newby. Thanks

**DonkeyOte** · 11-28-2010, 05:42 AM

@dwogn, welcome to the board however please note:

Your post does not comply with Rule 2 of our Forum RULES.
Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.