Private Sub for manual change to range of cells

Hello!

I am trying to create a macro that will run only when selected values in a sheet are manually modified. Currently, I have a private sub linked to the relevant worksheet, which detects change in the following fashion:

Please Login or Register  to view this content.

As you will have guessed, I have a problem because this macro runs whenever I manually enter a change but also when the VBA coding changes the target cells. And since the macro triggered by change to the target range also includes further changes to the target range, it runs continually and I need to stop it using Ctl + break.

Could anyone help me either find a way to differentiate between manual and automatic (i.e. VBA) change, or provide me with a different macro that will run only once if a value in the target range is changed?

Thank you very much for your time and help!

Regards,

Calypso

Hi,

Before you change the cell values in your code, you need to temporarily set Application.EnableEvents to False so that your Worksheet_Change event handler isn't called. You must ensure it is set back to True again once the changes have been made.

Yes, exactly. But you need to ensure that you turn EnableEvents back on even if an error occurs, so I would suggest a structure such as this:

Please Login or Register  to view this content.

There's one exit point in this routine, and Application.EnableEvents = True is always called.