Private sub problem
 

Hi

I have the following code in a excel worksheet, but the last bit does not run.
It should move data from one column to the previous one which works fine.
The second part should show when the data was last updated.

They both work separately but when I add them together I get an "ambiguous
error". I have renamed the second sub to what it is below instead of
"worksheet_change" as the first one but now the second sub does not run.

Can anybody help and explain why.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Cells(Target.Row, 3).Value = Cells(Target.Row, 5).Value
Cells(Target.Row, 5).Value = Cells(Target.Row, 4).Value
Application.EnableEvents = True

End Sub


Private Sub Worksheet_Date(ByVal Target As Excel.Range)

If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub


Thanks

SBY

Private sub problem
 

You don't get to name events - Excel only has predetermined events. Just change your first event to
include the time stamp:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Cells(Target.Row, 2).Value = Now()
Cells(Target.Row, 3).Value = Cells(Target.Row, 5).Value
Cells(Target.Row, 5).Value = Cells(Target.Row, 4).Value
Application.EnableEvents = True

End Sub


--
HTH,
Bernie
MS Excel MVP


"sby" wrote in message
...
Hi

I have the following code in a excel worksheet, but the last bit does not run.
It should move data from one column to the previous one which works fine.
The second part should show when the data was last updated.

They both work separately but when I add them together I get an "ambiguous
error". I have renamed the second sub to what it is below instead of
"worksheet_change" as the first one but now the second sub does not run.

Can anybody help and explain why.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Cells(Target.Row, 3).Value = Cells(Target.Row, 5).Value
Cells(Target.Row, 5).Value = Cells(Target.Row, 4).Value
Application.EnableEvents = True

End Sub


Private Sub Worksheet_Date(ByVal Target As Excel.Range)

If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub


Thanks

SBY