VBA how to avoid target looping?

[Kamolga]

Hi,

I have the targets that run my macros spread on 3 column (A,B,C let's say). My problem is that if you select a specific value (Product 123 for example) in A, B, or C (let's say B in this example) I need to put product 123 in A & C: a message pops up to ask if user wants this, if he says yes, the macro writes Product 123 in A which get's the message to come again to ask userif he wants to put the values in other B&C, which will again affect a target...etc.

Is there a way to tell excell to stop considering the 3 column as target when user click yes? Any other workaround?

 

[Smitty]

You can use:

Application.EnableEvents = False

Prior to changing the value, which will stop recursion. Just make sure to set it back to True at the end.

HTH,

 

[Jonmo1]

Turn off Events prior to any line of code that changes the value of cells.

Application.EnableEvents = False

Make sure to set it back to TRUE at the end of your code.
Also wise to use an error handler

Application.EnableEvents = False
On Error GoTo hndlr

'rest of your code goes here


'At the very end of the code put
hndlr:
Application.EnableEvents = True
End Sub

 

[Kamolga]

Thank you very very much indeed, that works perfectly

 

[Jonmo1]

You're welcome.