How to use now() in a special way

  • Thread starter The Fool on the Hill
  • Start date
T

The Fool on the Hill

Dear excel(lent) users

I have an actionlist in Excel with a couple of formulas which make it easier
to fill in the specific details.

When I enter something in field B2 a couple of other fields are filled out
automatically by these formula´s, such as the number of the action, or the
date of today. And this is were my question arises.

I have used the following formula:
=if(B2<>"";now();"")

The problem is with using now(), that it always fills in the actual date of
today. Meaning tomorrow now() is today+1. So if I look at the sheet tomorrow
it seems that I entered todays activity tomorrow instead of today.

Is there a way to avoid this from happening in a simple way??

Thanks kindly for helping me out in this matter.

Kind regards,

Jay
 
R

Roger Govier

Hi

Typing Control + semicolon (ctrl ;) will enter today's Date into a cell
as a fixed value.
Typing Control + Shift + semicolon (ctrl :) will enter Current Time into
a cell as a fixed value.

The following Macro will place the value of NOW() in the active cell as
a fixed value

Sub Timestamp()
ActiveCell.FormulaR1C1 = Now()

End Sub


You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

To run the macro, Tools>Macro>Macros>Select the macro>Run.

If you want to create a shortcut, then Tools>Macros>Select the
macro>Options>enter a key to use with Ctrl>OK


If you are new to entering macros, then David McRitchie has lots of
useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm



--
Regards

Roger Govier


"The Fool on the Hill" <[email protected]>
wrote in message
news:[email protected]...
 
M

Mike H

Hi,

A couple of ways.

1. The simplest is every day put the date in a cell using 13/7/2007 (not
Today() or Now() ) and refer to this with

=IF(B2<>"",H1,"") Note I've use , not ; but that's my version of Excel.

2. A different way would be to paste this code into the worksheet code:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("B2") '< Could be B:B for an entire column
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target <> "" Then Target.Offset(0, 1) = Date
If Target = "" Then Target.Offset(0, 1) = ""
End Sub

Alter the range and offsets to suit your needs.

Mike
 
T

The Fool on the Hill

I do not understand what is meant by offset and range, can you please specify?
 
M

Mike H

Hi,
The macro runs every time a cell is altered on the worksheet but it only
does something if a cell defined in the range is altered.

Range. In the macro the range is currently set to B2
Set rng = Range("B2")
What this means is that if you put something in B2 the Macro does something.
You could set the range to anything you want:-
Set rng = Range("B:B") any cell in column B
Set rng = Range("B2:D100") the block of cells B2 to D100
If any cells in this larger range are change then the macro does something.

Offset
Target.Offset(0, 1) = Date
When the macro does something it writes the value Date to cell offset from
the active(target) cell so offset(0,1) moves no rows up or down and 1 column
to the right.
offset(column,row) replace column/row with the number of columns/rows you
want to offset.

Hope this helps.

Mike
 
T

The Fool on the Hill

Hello Mike,

Thanks for your answer and patience with my ignorance.

When i enter something in the B:B range (for instance B4), then the date
must be entered in the H:H range (in this case H4).

I have tried to change your advice and this is what I have come up with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("B:B") '< Could be B:B for an entire column
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target <> "" Then Target.Offset(0, 6) = Now()
If Target = "" Then Target.Offset(0, 6) = ""
End Sub

It seems as if it does not work.

Thanks for your help !!
 
M

Mike H

Hi,

The changes you have made work perfectly for me so If it isn't working for
you then I suspect you may have put the code in the wrong place.

Right click the sheet tab, view code and paste it in there and see what
happens.

Mike
 
R

Roger Govier

Hi

That should work fine.
Maybe Application.EnableEvents has been switched off.

In the Immediate windows of the VBE (press Control + G to bring it up if
not visible), enter the following
Application.EnableEvents=True
and press Enter.

Then try again

--
Regards

Roger Govier


"The Fool on the Hill" <[email protected]>
wrote in message
news:[email protected]...
 
T

The Fool on the Hill

Hey Mike,

This works perfectly !!

Thanks mate !!

Have a great weekend !
 
T

The Fool on the Hill

Now I am trying to do the same for another one in the same sheet:

Private Sub Worksheet_Change2(ByVal Target As Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("J:J")
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target = "Ja" Then Target.Offset(0, 1) = Now()
If Target = "" Then Target.Offset(0, 1) = ""
If Target = "Nee" Then Target.Offset(0, 1) = ""
End Sub

Where Ja is equal to Yes and Nee is Equal to No. Again it does not work,
what am I doing wrong?
 
M

Mike H

Hi,

You must combine the 2 events into the worksheet change event not create a
second, try this it's a bit messy but the best I can think of:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
If Target.Count > 1 Then Exit Sub
Set rng1 = Range("B:B")
Set rng2 = Range("J:J")
If Intersect(Target, rng1) Is Nothing Then GoTo 100
If Target <> "" Then Target.Offset(0, 6) = Now()
If Target = "" Then Target.Offset(0, 6) = ""
100
If Intersect(Target, rng2) Is Nothing Then Exit Sub
If Target = "Ja" Then Target.Offset(0, 1) = Now()
If Target = "Nee" Then Target.Offset(0, 1) = ""
If Target.Value = "" Then Target.Offset(0, 1) = ""
End Sub

Mike
 
T

The Fool on the Hill

Excellent Mike,

Just like I want it !!

Great thanks for your help and patience !

Greetings and fine weekend !!

Jay
 
Top