Event recognition.

[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")) _
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

 

[Claus Busch]

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 :

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.

 

[Colin Hayes]

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.

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

 

[Claus Busch]

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.

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.

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.

 

[Claus Busch]

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.

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.

 

[Colin Hayes]

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

 

[Claus Busch]

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.

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.

 

[Colin Hayes]

Hi Colin,

Am Thu, 17 Oct 2013 19:11:53 +0100 schrieb Colin Hayes:


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.

Hi Claus

Yes , I see the sense in what you say.

It's a pity pasting several cells in the row from D can't be made to
trigger the date entry in C.



Best Wishes


Colin

 

[Claus Busch]

Hi Colin,

Am Thu, 17 Oct 2013 21:43:53 +0100 schrieb Colin Hayes:

It's a pity pasting several cells in the row from D can't be made to
trigger the date entry in C.

you could do it with a normal procedure instead an event procedure


Regards
Claus B.

 

[Colin Hayes]

Hi Colin,

Am Thu, 17 Oct 2013 21:43:53 +0100 schrieb Colin Hayes:


you could do it with a normal procedure instead an event procedure


Regards
Claus B.

Hi Claus

You mean a macro that had to be run each time? Or would it be embedded
in the ws coding?



Best Wishes


Colin

 

[Claus Busch]

Hi Colin,

Am Thu, 17 Oct 2013 22:39:32 +0100 schrieb Colin Hayes:

You mean a macro that had to be run each time? Or would it be embedded
in the ws coding?

whether you paste or you delete the range is always selected. So you
could run following macro:

Sub WriteDate()
Dim rngC As Range

For Each rngC In Selection
rngC.Offset(, -1) = IIf(Len(rngC) > 0, Date, "")
Next
End Sub

You can create a shortcut to run the macro. Handling would be easier
with shortcut


Regards
Claus B.

 

[Colin Hayes]

Sub WriteDate()
Dim rngC As Range

For Each rngC In Selection
rngC.Offset(, -1) = IIf(Len(rngC) > 0, Date, "")
Next
End Sub

You can create a shortcut to run the macro. Handling would be easier with shortcut


Regards
Claus B.

Hi Claus

Yes , this is a good solution. Thanks.

It does put the date in all cells of the selected range , rather than
only in the cell to the left , but this is accommodated by selecting a
single cell rather than the pasted range.

Thanks again Claus.



Best Wishes