Excel cell updates

Microsoft Office excel 2007
August 6, 2010 at 12:42:16
Specs: Windows Vista
I have a cell with a basic formula, "=now()", which displays the time and date in a specified format.

How can I keep that cell from updating automatically every time I open the file? I have a macro set to calculate the value on command, but it also updates on open, which I don't want.

Thanks in advance,
-Joe


See More: Excel cell updates

Report •


#1
August 6, 2010 at 13:14:28
Hi,

NOW() is one of Excel's volatile functions. It updates on Open and Close and pretty much on all changes in the workbook that affect calculation.

You can't change that functionality.

You can use a macro to enter a date/time in a cell, as a value (not as a formula) and it will never change.

Please post the code in your macro, for further advice.

Regards


Report •

#2
August 6, 2010 at 13:33:01
Very simple, one line.

Range("C4").Calculate

It causes it to recalculate the "=now()" function.

How do I make a macro write the value into a cell that would come up using "=now()" ?

Thanks,
-Joe


Report •

#3
August 6, 2010 at 15:25:12
Hi,

In VBA these two lines will enter a time and set the format:

Range("C4").Value = Time
Range("C4").NumberFormat = "hh:mm"

The time in cell C4 will not change unless this bit of code is run.
It will not change when the workbook is opened or closed etc.

Regards


Report •

Related Solutions

#4
August 6, 2010 at 15:40:01
Is there a command I can substitute for "Time" that will display both the date and the time?

Report •

#5
August 6, 2010 at 15:48:12
Never mind, I just tried using "now" and it worked. Thanks for your help.

Report •

#6
August 6, 2010 at 15:53:03
You're welcome

Regards

Humar


Report •

#7
August 7, 2010 at 07:01:02
Now how do I get the macro to do several different things? Like all 3 of these? It doesn't work to just have those one after the other.

Range("C4").Value = Now
Range("C4").NumberFormat = "yymmddhhmm"

Range("C1").Value = Today
Range("C1").NumberFormat = "mm/dd/yyyy"

Range("A51").Value = Today
Range("A51").NumberFormat = "mm/dd/yyyy"

Thanks again.


Report •

#8
August 7, 2010 at 07:22:36
Hi,

If you had used Option Explicit like this:

Option Explicit

Sub Times()
Range("C4").Value = Now
Range("C4").NumberFormat = "yymmddhhmm"

Range("C1").Value = Today
Range("C1").NumberFormat = "mm/dd/yyyy"

Range("A51").Value = Today
Range("A51").NumberFormat = "mm/dd/yyyy"
End Sub

You would have received a 'Variable not defined' error for 'Today'

That's because Today is not a VBA command and VBA has treated it as a variable with no value.

So the macro ran all the lines - C1 and A51 have been formatted, but no value was entered in those cells.

As Date information is in the Integer part of the value returned by Now, try this:

Option Explicit

Sub Times()
Range("C4").Value = Now
Range("C4").NumberFormat = "yymmddhhmm"

Range("C1").Value = Int(Now)
Range("C1").NumberFormat = "mm/dd/yyyy"

Range("A51").Value = Int(Now)
Range("A51").NumberFormat = "mm/dd/yyyy"
End Sub

or use the VBA Date function:

Option Explicit

Sub Times()
Range("C4").Value = Now
Range("C4").NumberFormat = "yymmddhhmm"

Range("C1").Value = Date
Range("C1").NumberFormat = "mm/dd/yyyy"

Range("A51").Value = Date
Range("A51").NumberFormat = "mm/dd/yyyy"
End Sub

Regards


Report •

#9
August 7, 2010 at 07:32:07
Didn't know you couldn't use "Today" in VB (I'm new to this, as you can tell).

Here's the script that worked for what I needed to do:

Sub GeneratePONumber()
Range("D4").Value = Now
Range("D4").NumberFormat = "yymmddhhmm"

Range("C1").Value = Now
Range("C1").NumberFormat = "mm/dd/yyyy"

Range("A51").Value = Now
Range("A51").NumberFormat = "mm/dd/yyyy"
End Sub

Thanks,
-Joe


Report •

#10
August 7, 2010 at 07:49:27
Hi,

You realize that when you use Now you are putting both a date and a time value in each cell.

It looks from your formatting that you only want the date information in cells C1 and A51.

In which case use Date not Now

Now will work, but you may get unexpected results later

If you run this:

Range("A1").Value = Now
Range("A1").NumberFormat = "mm/dd/yyyy"

Then edit the same code to this:

Range("B1").Value = Now
Range("B1").NumberFormat = "mm/dd/yyyy"
and run it.
(Don't try this with all 4 lines in one subroutine - you need to run it twice - once for col. A and once for col. B)

In the worksheet, cells A1 and B1 will both show today's date - they will appear identical.

In another cell enter this formula:

=IF(A1=B1,"Equal","Not equal")

The result is 'Not equal'

If you do the same with Date instead of Now, the result will be 'Equal'

Regards


Report •


Ask Question