Worksheet_Change Macro will not finish running

[KiwiRiccardo]

All,

I don't often have to use VBA so don't really know what I am doing however I seem to have come up with some code that works other than just continues to run. I can sort of see why it does this but was wondering if anyone could give me a point in the right direction.

The code enters a date stamp in column 10 whenever any values are changed in the row and it also copyies and pastes values back for the data in column 11 when the value in column 9 = Closed. There is a formula in column 11 and when the record is closed the current value needs to be recorded at the time of closure.

Can anyone help please as I have really tried to figure this out.

Cheers

Rich

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 1 Then Exit Sub
If Target.Row = 2 Then Exit Sub
If Target.Column = 10 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Cells(Target.Row, 10) = Now
If Cells(Target.Row, 9) = "Closed" Then
Cells(Application.ActiveCell.Row, 11).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

End Sub

 

[SkipVought]

Hi,

Think about it!

You have a macro that runs each time a change occurs on the worksheet, that CHANGES the worksheet.

Do you see a problem with that?

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Row = 1 Then Exit Sub   
If Target.Row = 2 Then Exit Sub
If Target.Column = 10 Then Exit Sub    
If IsEmpty(Target) Then Exit Sub     
Cells(Target.Row, 10) = Now      
If Cells(Target.Row, 9) = "Closed" Then
Cells(Application.ActiveCell.Row, 11).Select
Selection.Copy
[b]application.enableevents = false[/b]
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
[b]application.enableevents = true[/b]
End If
 
End Sub

You have to stop the recursion.



Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[KiwiRiccardo]

Skip,

That is great, thank you for your help.

Rich