excessive calcs for simple macro . . .
 

I am using the simple code below to insert a timestamp into column 2 adjacent
to an entry in column one. The macro code works, but Excel needlessly
calculates for about 30 seconds after it has placed the timestamp. My
colleague took the code to his workstation and claimed it worked narmally
there. We compared Excels settings on the two machines and found nothing
obvious.

Might anyone have a clue why it does this and how I might stop it? (or
another way to code to get the same effect?)

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Selection.Column
Case 1
Cells(Target.Row, 2).Value = Now

End Select

End Sub

excessive calcs for simple macro . . .
 

You are getting re-entries. Better is:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Selection.Column
Case 1
Application.EnableEvents = False
Cells(Target.Row, 2).Value = Now
Application.EnableEvents = True
End Select

End Sub

--
Gary''s Student - gsnu200778


"mike" wrote:

I am using the simple code below to insert a timestamp into column 2 adjacent
to an entry in column one. The macro code works, but Excel needlessly
calculates for about 30 seconds after it has placed the timestamp. My
colleague took the code to his workstation and claimed it worked narmally
there. We compared Excels settings on the two machines and found nothing
obvious.

Might anyone have a clue why it does this and how I might stop it? (or
another way to code to get the same effect?)

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Selection.Column
Case 1
Cells(Target.Row, 2).Value = Now

End Select

End Sub

excessive calcs for simple macro . . .
 

That did the trick, thanks!

"Gary''s Student" wrote:

You are getting re-entries. Better is:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Selection.Column
Case 1
Application.EnableEvents = False
Cells(Target.Row, 2).Value = Now
Application.EnableEvents = True
End Select

End Sub

--
Gary''s Student - gsnu200778


"mike" wrote:

I am using the simple code below to insert a timestamp into column 2 adjacent
to an entry in column one. The macro code works, but Excel needlessly
calculates for about 30 seconds after it has placed the timestamp. My
colleague took the code to his workstation and claimed it worked narmally
there. We compared Excels settings on the two machines and found nothing
obvious.

Might anyone have a clue why it does this and how I might stop it? (or
another way to code to get the same effect?)

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Selection.Column
Case 1
Cells(Target.Row, 2).Value = Now

End Select

End Sub