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

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, ToolsMacroMacrosSelect the macroRun.

If you want to create a shortcut, then ToolsMacrosSelect the
macroOptionsenter a key to use with CtrlOK


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"
wrote in message
...
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

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

"The Fool on the Hill" wrote:

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

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

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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

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



"The Fool on the Hill" wrote:

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

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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

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 !!

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

"The Fool on the Hill" wrote:

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 !!

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"
wrote in message
...
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 !!

Hey Mike,

This works perfectly !!

Thanks mate !!

Have a great weekend !

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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 !!

You seem very pleased!! and your welcome, thanks for the feedback.

"The Fool on the Hill" wrote:

Hey Mike,

This works perfectly !!

Thanks mate !!

Have a great weekend !

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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 !!

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?


"Mike H" wrote:

You seem very pleased!! and your welcome, thanks for the feedback.

"The Fool on the Hill" wrote:

Hey Mike,

This works perfectly !!

Thanks mate !!

Have a great weekend !

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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 !!

Hello Roger,

Thanks for your input, I appreciate it.

Jay

"Roger Govier" wrote:

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"
wrote in message
...
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 !!

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

"The Fool on the Hill" wrote:

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?


"Mike H" wrote:

You seem very pleased!! and your welcome, thanks for the feedback.

"The Fool on the Hill" wrote:

Hey Mike,

This works perfectly !!

Thanks mate !!

Have a great weekend !

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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 !!

Excellent Mike,

Just like I want it !!

Great thanks for your help and patience !

Greetings and fine weekend !!

Jay

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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?


"Mike H" wrote:

You seem very pleased!! and your welcome, thanks for the feedback.

"The Fool on the Hill" wrote:

Hey Mike,

This works perfectly !!

Thanks mate !!

Have a great weekend !

"Mike H" wrote:

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

"The Fool on the Hill" wrote:

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 !!