Macro to insert Now() when cell updates

[systematic]

Hi there,

I'm trying to write a macro, so that if a cell in a range has a value placed in it - Now() is written to a corresponding cell in another column, then the value copied and pasted (so that it does not update).

I have this -

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "F6:F45" Then
Application.EnableEvents = False

If Range("F6:F45") = "1" Then
Range("D6:D45").Value = Now()

Else
Range("D6:D45") = "No call taken"

End If

Application.EnableEvents = True

End If

Range("D6:D45").Select
Selection.Copy
Range("D6:D45").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

For some reason it does not want to work for me. I have EnableEvents set to True on workbook open and am running Office XP.

Would appreciate any advice to steer me in the right direction as I am still quite new to VBA!

Thanks

Rob

[Norman Jones]

Hi Rob,

Try something like:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range

Set rng = Me.Range("F6:F45")

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(rng, .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
.Offset(0, 1).Value = FormatDateTime(Now, vbLongTime)
End If
Application.EnableEvents = True
End If
End With
End Sub
'<<=============


---
Regards,
Norman


"systematic" <[email protected]> wrote
in message news:[email protected]...
>
> Hi there,
>
> I'm trying to write a macro, so that if a cell in a range has a value
> placed in it - Now() is written to a corresponding cell in another
> column, then the value copied and pasted (so that it does not update).
>
> I have this -
>
> private sub worksheet_change(byval target as range)
>
> if target.address(false, false) = \"f6:f45\" then
> application.enableevents = false
>
> if range(\"f6:f45\") = \"1\" then
> range(\"d6:d45\").value = now()
>
> else
> range(\"d6:d45\") = \"no call taken\"
>
> end if
>
> application.enableevents = true
>
> end if
>
> range(\"d6:d45\").select
> selection.copy
> range(\"d6:d45\").select
> selection.pastespecial paste:=xlpastevalues, operation:=xlnone,
> skipblanks _
> :=false, transpose:=false
>
> end sub
>
> For some reason it does not want to work for me. I have EnableEvents
> set to True on workbook open and am running Office XP.
>
> Would appreciate any advice to steer me in the right direction as I am
> still quite new to VBA!
>
> Thanks
>
> Rob
>
>
> --
> systematic
> ------------------------------------------------------------------------
> systematic's Profile:
> http://www.excelforum.com/member.php...o&userid=25294
> View this thread: http://www.excelforum.com/showthread...hreadid=496107
>

[Martin Fishlock]

Norman,

Thanks for the answer to systematic's question.

Trying the code out it works well if the cell is directly entered. But if
you copy down or you paste into more than one cell it does not enter the date.

Is there any solution to this problem?

TIA.



"Norman Jones" wrote:

> Hi Rob,
>
> Try something like:
>
> '=============>>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Dim rng As Range
>
> Set rng = Me.Range("F6:F45")
>
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(rng, .Cells) Is Nothing Then
> Application.EnableEvents = False
> If Not IsEmpty(.Value) Then
> .Offset(0, 1).Value = FormatDateTime(Now, vbLongTime)
> End If
> Application.EnableEvents = True
> End If
> End With
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> "systematic" <[email protected]> wrote
> in message news:[email protected]...
> >
> > Hi there,
> >
> > I'm trying to write a macro, so that if a cell in a range has a value
> > placed in it - Now() is written to a corresponding cell in another
> > column, then the value copied and pasted (so that it does not update).
> >
> > I have this -
> >
> > private sub worksheet_change(byval target as range)
> >
> > if target.address(false, false) = \"f6:f45\" then
> > application.enableevents = false
> >
> > if range(\"f6:f45\") = \"1\" then
> > range(\"d6:d45\").value = now()
> >
> > else
> > range(\"d6:d45\") = \"no call taken\"
> >
> > end if
> >
> > application.enableevents = true
> >
> > end if
> >
> > range(\"d6:d45\").select
> > selection.copy
> > range(\"d6:d45\").select
> > selection.pastespecial paste:=xlpastevalues, operation:=xlnone,
> > skipblanks _
> > :=false, transpose:=false
> >
> > end sub
> >
> > For some reason it does not want to work for me. I have EnableEvents
> > set to True on workbook open and am running Office XP.
> >
> > Would appreciate any advice to steer me in the right direction as I am
> > still quite new to VBA!
> >
> > Thanks
> >
> > Rob
> >
> >
> > --
> > systematic
> > ------------------------------------------------------------------------
> > systematic's Profile:
> > http://www.excelforum.com/member.php...o&userid=25294
> > View this thread: http://www.excelforum.com/showthread...hreadid=496107
> >
>
>
>

[Ken Johnson]

Hi Bill,
Is what you are trying to do really going to overcome the problem of
each Now being updated or overwritten?. When you use VBA to place Now
in a cell it doesn't put the Now function into that cell it just puts
in the current Now value, ie a static date and time value. Each time
your code runs it will update the cell with the new Now value and you
lose the old one. If you don't want to lose any old Now values you will
need to use the Offset method to shift all the older Now values across
one column or down one row.
For example: when the following code is run it puts the current Now
value into B1 then shifts all previous Now values down one row by way
of the Offset method copying each value into the cell immediately
below. When the loop reaches B1 that too is copied into B2.

Public Sub SaveOldNows()
Range("B1").Value = Now
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
Next iRowOffset
End Sub

Also, what is the effect of the "\"'s in your code. I've never seen
anything like it before?
<if range(\"f6:f45\") = \"1\" then >

Also, in the first line of your code are you intending to detect if the
sheet change has occurred anywhere in the range F6:F45?
If that is the case then the way I have seen it done is...

If Intersect(Target,Range("F6:F45")) Is Nothing then
Exit Sub
End If

Hope this helps you.

Ken Johnson

[Ken Johnson]

Hi Bill,
Is what you are trying to do really going to overcome the problem of
each Now being updated or overwritten?. When you use VBA to place Now
in a cell it doesn't put the Now function into that cell it just puts
in the current Now value, ie a static date and time value. Each time
your code runs it will update the cell with the new Now value and you
lose the old one. If you don't want to lose any old Now values you will
need to use the Offset method to shift all the older Now values across
one column or down one row.
For example: when the following code is run it puts the current Now
value into B1 then shifts all previous Now values down one row by way
of the Offset method copying each value into the cell immediately
below. When the loop reaches B1 that too is copied into B2.

Public Sub SaveOldNows()
Range("B1").Value = Now
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
Next iRowOffset
End Sub

Also, what is the effect of the "\"'s in your code. I've never seen
anything like it before?
<if range(\"f6:f45\") = \"1\" then >

Also, in the first line of your code are you intending to detect if the
sheet change has occurred anywhere in the range F6:F45?
If that is the case then the way I have seen it done is...

If Intersect(Target,Range("F6:F45")) Is Nothing then
Exit Sub
End If

Hope this helps you.

Ken Johnson

[Ken Johnson]

Hi Rob,
Is what you are trying to do really going to overcome the problem of
each Now being updated or overwritten?. When you use VBA to place Now
in a cell it doesn't put the Now function into that cell it just puts
in the current Now value, ie a static date and time value. Each time
your code runs it will update the cell with the new Now value and you
lose the old one. If you don't want to lose any old Now values you will
need to use the Offset method to shift all the older Now values across
one column or down one row.
For example: when the following code is run it puts the current Now
value into B1 then shifts all previous Now values down one row by way
of the Offset method copying each value into the cell immediately
below. When the loop reaches B1 that too is copied into B2.

Public Sub SaveOldNows()
Range("B1").Value = Now
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
Next iRowOffset
End Sub

Also, what is the effect of the "\"'s in your code. I've never seen
anything like it before?
<if range(\"f6:f45\") = \"1\" then >

Also, in the first line of your code are you intending to detect if the
sheet change has occurred anywhere in the range F6:F45?
If that is the case then the way I have seen it done is...

If Intersect(Target,Range("F6:F45")) Is Nothing then
Exit Sub
End If

Hope this helps you.

Ken Johnson

[Ken Johnson]

Hi Rob,
Is what you are trying to do really going to overcome the problem of
each Now being updated or overwritten?. When you use VBA to place Now
in a cell it doesn't put the Now function into that cell it just puts
in the current Now value, ie a static date and time value. Each time
your code runs it will update the cell with the new Now value and you
lose the old one. If you don't want to lose any old Now values you will
need to use the Offset method to shift all the older Now values across
one column or down one row.
For example: when the following code is run it puts the current Now
value into B1 then shifts all previous Now values down one row by way
of the Offset method copying each value into the cell immediately
below. When the loop reaches B1 that too is copied into B2.

Public Sub SaveOldNows()
Range("B1").Value = Now
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
Next iRowOffset
End Sub

Also, what is the effect of the "\"'s in your code. I've never seen
anything like it before?
<if range(\"f6:f45\") = \"1\" then >

Also, in the first line of your code are you intending to detect if the
sheet change has occurred anywhere in the range F6:F45?
If that is the case then the way I have seen it done is...

If Intersect(Target,Range("F6:F45")) Is Nothing then
Exit Sub
End If

Hope this helps you.

Ken Johnson

[Ken Johnson]

Hi Rob,
Is what you are trying to do really going to overcome the problem of
each Now being updated or overwritten?. When you use VBA to place Now
in a cell it doesn't put the Now function into that cell it just puts
in the current Now value, ie a static date and time value. Each time
your code runs it will update the cell with the new Now value and you
lose the old one. If you don't want to lose any old Now values you will
need to use the Offset method to shift all the older Now values across
one column or down one row.
For example: when the following code is run it puts the current Now
value into B1 then shifts all previous Now values down one row by way
of the Offset method copying each value into the cell immediately
below. When the loop reaches B1 that too is copied into B2.

Public Sub SaveOldNows()
Range("B1").Value = Now
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
Next iRowOffset
End Sub

Also, what is the effect of the "\"'s in your code. I've never seen
anything like it before?
<if range(\"f6:f45\") = \"1\" then >

Also, in the first line of your code are you intending to detect if the
sheet change has occurred anywhere in the range F6:F45?
If that is the case then the way I have seen it done is...

If Intersect(Target,Range("F6:F45")) Is Nothing then
Exit Sub
End If

Hope this helps you.

Ken Johnson

[John Coleman]

systematic wrote:
> Hi there,
>
> I'm trying to write a macro, so that if a cell in a range has a value
> placed in it - Now() is written to a corresponding cell in another
> column, then the value copied and pasted (so that it does not update).
>
> I have this -
>
> private sub worksheet_change(byval target as range)
>
> if target.address(false, false) = \"f6:f45\" then
> application.enableevents = false
>
> if range(\"f6:f45\") = \"1\" then
> range(\"d6:d45\").value = now()
>
> else
> range(\"d6:d45\") = \"no call taken\"
>
> end if
>
> application.enableevents = true
>
> end if
>
> range(\"d6:d45\").select
> selection.copy
> range(\"d6:d45\").select
> selection.pastespecial paste:=xlpastevalues, operation:=xlnone,
> skipblanks _
> :=false, transpose:=false
>
> end sub
>
> For some reason it does not want to work for me. I have EnableEvents
> set to True on workbook open and am running Office XP.
>
> Would appreciate any advice to steer me in the right direction as I am
> still quite new to VBA!
>
> Thanks
>
> Rob

Hi

try


Private Sub Worksheet_Change(ByVal Target As Range)


If Union(Target, Range("$F$6:$F$45")).Address = "$F$6:$F$45" Then
Application.EnableEvents = False
If Target.Value = 1 Then
Target.Offset(0, -2).Value = Now
Else
Target.Offset(0, -2).Value = "no call taken"
End If
Application.EnableEvents = True
End If

End Sub

Note the following changes:

1) VBA has its own version of Now() - no need to bother with paste
special

2) \ is not an escape character in VBA \" throws a syntax error

3) unless you have a reason to use relative addresses - just accept the
default behavior of range.address and insert $ where needed. To get a
feel of how things work, play around with the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub

4) If you change *one* cell in F6:F45, then the address of the target
will be that of the changed cell and not all of F6:F45. If you want to
monitor a range for any change, you need to use the union operator as
above

5) the offset from the target has the effect of only changing the
corresponding cell in the D column, and not the entire range of cells

6) I always use the slightly verbose range.value rather than just range
(which defaults to value) - but this is a matter of taste. I find
explicit code easier to debug

7) I assume you didn't really want "1" for the value (as opposed to 1)
- if you really wanted the string you can revert to "1"

If you are new to VBA then Walkenbach's VBA books are great learning
tools.

Hope that helps

-John Coleman

[Ken Johnson]

Hi Rob,
Is what you are trying to do really going to overcome the problem of
each Now being updated or overwritten?. When you use VBA to place Now
in a cell it doesn't put the Now function into that cell it just puts
in the current Now value, ie a static date and time value. Each time
your code runs it will update the cell with the new Now value and you
lose the old one. If you don't want to lose any old Now values you will
need to use the Offset method to shift all the older Now values across
one column or down one row.
For example: when the following code is run it puts the current Now
value into B1 then shifts all previous Now values down one row by way
of the Offset method copying each value into the cell immediately
below. When the loop reaches B1 that too is copied into B2.

Public Sub SaveOldNows()
Range("B1").Value = Now
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
Next iRowOffset
End Sub

Also, what is the effect of the "\"'s in your code. I've never seen
anything like it before?
<if range(\"f6:f45\") = \"1\" then >

Also, in the first line of your code are you intending to detect if the
sheet change has occurred anywhere in the range F6:F45?
If that is the case then the way I have seen it done is...

If Intersect(Target,Range("F6:F45")) Is Nothing then
Exit Sub
End If

Hope this helps you.

Ken Johnson

[Norman Jones]

Hi Martin,


> Trying the code out it works well if the cell is directly entered. But if
> you copy down or you paste into more than one cell it does not enter
> the date.
>
> Is there any solution to this problem?

The code was specifically limited to a single cell by the line:

>> If .Count > 1 Then Exit Sub


> Is there any solution to this problem?

Try something like::

'=============>>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim rCell As Range

Set rng = Intersect(Target, Me.Range("F6:F45"))

Application.EnableEvents = False
For Each rCell In rng
With rCell
If Not Intersect(rng, .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
With .Offset(0, 1)
If IsEmpty(.Value) Then
.Value = FormatDateTime(Now, vbLongTime)
End If
End With
End If
End If
End With
Next rCell
Application.EnableEvents = True
End Sub
'<<=============


---
Regards,
Norman


"Martin Fishlock" <[email protected]> wrote in
message news:[email protected]...
> Norman,
>
> Thanks for the answer to systematic's question.
>
> Trying the code out it works well if the cell is directly entered. But if
> you copy down or you paste into more than one cell it does not enter the
> date.
>
> Is there any solution to this problem?
>
> TIA.
>
>
>
> "Norman Jones" wrote:
>
>> Hi Rob,
>>
>> Try something like:
>>
>> '=============>>
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> Dim rng As Range
>>
>> Set rng = Me.Range("F6:F45")
>>
>> With Target
>> If .Count > 1 Then Exit Sub
>> If Not Intersect(rng, .Cells) Is Nothing Then
>> Application.EnableEvents = False
>> If Not IsEmpty(.Value) Then
>> .Offset(0, 1).Value = FormatDateTime(Now, vbLongTime)
>> End If
>> Application.EnableEvents = True
>> End If
>> End With
>> End Sub
>> '<<=============
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "systematic" <[email protected]>
>> wrote
>> in message
>> news:[email protected]...
>> >
>> > Hi there,
>> >
>> > I'm trying to write a macro, so that if a cell in a range has a value
>> > placed in it - Now() is written to a corresponding cell in another
>> > column, then the value copied and pasted (so that it does not update).
>> >
>> > I have this -
>> >
>> > private sub worksheet_change(byval target as range)
>> >
>> > if target.address(false, false) = \"f6:f45\" then
>> > application.enableevents = false
>> >
>> > if range(\"f6:f45\") = \"1\" then
>> > range(\"d6:d45\").value = now()
>> >
>> > else
>> > range(\"d6:d45\") = \"no call taken\"
>> >
>> > end if
>> >
>> > application.enableevents = true
>> >
>> > end if
>> >
>> > range(\"d6:d45\").select
>> > selection.copy
>> > range(\"d6:d45\").select
>> > selection.pastespecial paste:=xlpastevalues, operation:=xlnone,
>> > skipblanks _
>> > :=false, transpose:=false
>> >
>> > end sub
>> >
>> > For some reason it does not want to work for me. I have EnableEvents
>> > set to True on workbook open and am running Office XP.
>> >
>> > Would appreciate any advice to steer me in the right direction as I am
>> > still quite new to VBA!
>> >
>> > Thanks
>> >
>> > Rob
>> >
>> >
>> > --
>> > systematic
>> > ------------------------------------------------------------------------
>> > systematic's Profile:
>> > http://www.excelforum.com/member.php...o&userid=25294
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=496107
>> >
>>
>>
>>

[Tom Ogilvy]

Sure, you can loop through the entire range of interest each time and update
cells where a date has not been entered as required ( a value is in a cell
within the range.). You could prevent copying into the range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Me.Range("F6:F45")

If Not Intersect(Target, rng) Is Nothing Then
Application.CutCopyMode = False
End If
End Sub

You could make a formula dependent on the range and utilize the calculate
event to clean up the range

You could use the selection change to affect the ability to drag

I don't know if any combination would be foolproof, but you could certainly
come close.

Obviously the more defensive/usually unnecessary checking you do, the more
it will affect performance.

--
Regards,
Tom Ogilvy




"Martin Fishlock" <[email protected]> wrote in
message news:[email protected]...
> Norman,
>
> Thanks for the answer to systematic's question.
>
> Trying the code out it works well if the cell is directly entered. But if
> you copy down or you paste into more than one cell it does not enter the
date.
>
> Is there any solution to this problem?
>
> TIA.
>
>
>
> "Norman Jones" wrote:
>
> > Hi Rob,
> >
> > Try something like:
> >
> > '=============>>
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > Dim rng As Range
> >
> > Set rng = Me.Range("F6:F45")
> >
> > With Target
> > If .Count > 1 Then Exit Sub
> > If Not Intersect(rng, .Cells) Is Nothing Then
> > Application.EnableEvents = False
> > If Not IsEmpty(.Value) Then
> > .Offset(0, 1).Value = FormatDateTime(Now, vbLongTime)
> > End If
> > Application.EnableEvents = True
> > End If
> > End With
> > End Sub
> > '<<=============
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> > "systematic" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > Hi there,
> > >
> > > I'm trying to write a macro, so that if a cell in a range has a value
> > > placed in it - Now() is written to a corresponding cell in another
> > > column, then the value copied and pasted (so that it does not update).
> > >
> > > I have this -
> > >
> > > private sub worksheet_change(byval target as range)
> > >
> > > if target.address(false, false) = \"f6:f45\" then
> > > application.enableevents = false
> > >
> > > if range(\"f6:f45\") = \"1\" then
> > > range(\"d6:d45\").value = now()
> > >
> > > else
> > > range(\"d6:d45\") = \"no call taken\"
> > >
> > > end if
> > >
> > > application.enableevents = true
> > >
> > > end if
> > >
> > > range(\"d6:d45\").select
> > > selection.copy
> > > range(\"d6:d45\").select
> > > selection.pastespecial paste:=xlpastevalues, operation:=xlnone,
> > > skipblanks _
> > > :=false, transpose:=false
> > >
> > > end sub
> > >
> > > For some reason it does not want to work for me. I have EnableEvents
> > > set to True on workbook open and am running Office XP.
> > >
> > > Would appreciate any advice to steer me in the right direction as I am
> > > still quite new to VBA!
> > >
> > > Thanks
> > >
> > > Rob
> > >
> > >
> > > --
> > > systematic
> >
> ------------------------------------------------------------------------
> > > systematic's Profile:
> > > http://www.excelforum.com/member.php...o&userid=25294
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=496107
> > >
> >
> >
> >

[Remy]

First of all, what is exactly not working?

Here a few tips:
I would break the procedure up in different parts and see if the
individual functionality works.
Not sure if it makes sense to compare entire ranges, maybe it's better
to just compare individual cells? Why do you compare it to 1?
Then, you also copy the whole range again, not sure if this is what you
wanna do. I think you only wanna copy the new cell, no?

Cheers
Remy Blaettler
http://www.collaboral.com