Event recognition.

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

I use this code to look at cells in column D. If any has new content ,
then it puts the date in the adjacent cell in column C :


Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error GoTo enditall
Application.EnableEvents = False
If Intersect(Range(Target(1).Address), _
Range("D:D")) _
Is Nothing Then GoTo enditall
With Target
If .Value <> "" Then
With .Offset(0, -1)

'Put in todays date :

.Value = Date

End With
Else: .Offset(0, -1).Value = ""
End If
End With
enditall:
Application.EnableEvents = True
End Sub


Unfortunately , it doesn't work when pasting content from other cells
into the cell in D. It only works when making direct typed entries. Can
someone suggest an amendment which would update after any entry (pasted
or directly entered) to the cell in D?



Best Wishes
 
Hi Colin,

Am Thu, 17 Oct 2013 14:43:08 +0100 schrieb Colin Hayes:
I use this code to look at cells in column D. If any has new content ,
then it puts the date in the adjacent cell in column C :
Click to expand...

try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

If Target.Column <> 4 Or Target.Count > 1 Then Exit Sub

If Target <> "" Then Target.Offset(, -1) = Date

End Sub


Regards
Claus B.
 
Claus Busch said:
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

If Target.Column <> 4 Or Target.Count > 1 Then Exit Sub

If Target <> "" Then Target.Offset(, -1) = Date

End Sub


Regards
Claus B.
Click to expand...


Hi Claus

OK Thanks for this.

I tried it out , and it still won't insert the date in C where the
content is pasted into D.

It works fine with direct entry , as before. Also , I find it doesn't
remove the content in C when the content in D is removed. I tried a few
times.

Thanks Claus.

Best Wishes


Colin
 
Hi Colin,

Am Thu, 17 Oct 2013 15:34:50 +0100 schrieb Colin Hayes:
I tried it out , and it still won't insert the date in C where the
content is pasted into D.
Click to expand...

for me it is also working if content is pasted.
It works fine with direct entry , as before. Also , I find it doesn't
remove the content in C when the content in D is removed. I tried a few
times.
Click to expand...

to remove the date if D is deleted change the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

If Target.Column <> 4 Or Target.Count > 1 Then Exit Sub

Target.Offset(, -1) = IIf(Target <> "", Date, "")

End Sub


Regards
Claus B.
 
Hi Colin,

Am Thu, 17 Oct 2013 15:34:50 +0100 schrieb Colin Hayes:
I tried it out , and it still won't insert the date in C where the
content is pasted into D.
Click to expand...

perhaps you have the events disabled.
Put following code in a standard module and run it:
Sub Test()
Application.EnableEvents = True
End Sub


Regards
Claus B.
 
to remove the date if D is deleted change the code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

If Target.Column <> 4 Or Target.Count > 1 Then Exit Sub

Target.Offset(, -1) = IIf(Target <> "", Date, "")

End Sub


Regards
Claus B.
Click to expand...

Hi Claus

OK I think I've found what the issue is. It works fine on direct entry ,
and for pasting of a single cell. If I want to past more than one cell
then it doesn't react.

So if I paste E1 to D1 then C1 reacts with the date.

If I paste E1:M1 to D1:L1 then C1 doesn't react.

This is where the problem is.

I wonder also if the enable.event command could be built into the code.

Very interesting. Thanks Claus.

Best Wishes


Colin
 
Hi Colin,

Am Thu, 17 Oct 2013 19:11:53 +0100 schrieb Colin Hayes:
OK I think I've found what the issue is. It works fine on direct entry ,
and for pasting of a single cell. If I want to past more than one cell
then it doesn't react.
Click to expand...

you get an error if you change many cells in time with Worksheet_Change
or Workbook_SheetChange event.
Therefore I wrote "If Target.count > 1 then Exit Sub" into the code.


Regards
Claus B.
 
Back
Top