How do I prevent macro from looping

[Kevlar]

I want to trigger a macro called "AddC" whenever I enter data into
cell and press enter.

I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Address = "$D$22" Then
AddC
End If

End Sub

the code for Addc is:

Range("W22").Select
Selection.Copy
Range("D22").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("D23").Select


End Sub

When it fires it goes into a continual loop. Why does it do this an
how do I prevent the loop

 

[Frank Kabel]

Hi
add the line
application.enableevents = false
at the beginning you your procedure

and
application.enableevents = True
at the endm you your procedure

 

[jeff]

Hi, Kevlar:

It does the looping because your macro performs
actions that fire the very event that causes your
macro to run in the first place.

I believe you need to add:

application.EnableEvents = False

at the top of your macro
and be sure to set it to true before exiting.

jeff

 

[AlfD]

HI!

You enter data in D22. You then ask your macro to overwrite it with
data from W22.

Two things: obviously this will cause a loop (as others have
explained).

But is that actually what you are wanting to achieve ( or would you,
for example, prefer it to take your newly-entered data from D22 and put
it in W22. If not, don't bother with data for D22:: just key Enter.

Alf

 

[Kevlar]

I needed to change

Sub Worksheet_SelectionChange

to:

Sub Worksheet_Change

to have the macro run only when I entered data into the cell.

Thanks for your help. That worked