Can you detect if and excel find and replace is active during worksheet_change()?

Question

Hi I've just crashed excel using amazon spreadsheet to update feed.

When doing find and replace [replace all] with 2 cells selected after the first replacement the worksheet_change() function finished with the whole spreadsheet selected. This meant that the replacements took place outside of the original area. Unfortunatly the replcement text included the find text and each replacement re-selected the entire area excel ran until it ran out of space then crashed.

Pressing control-break brings up the vba dialog STOP/CONTINUE/DEBUG. DEBUG is greyed out as amazon had protected the sheet. STOP would stop one run but would then continue to crash. CONTINUE would switch back to the current change and continue to crash.

Is there any way to detect if a find&replace operation is in action whilst executing excel vba?

Regards John

Answer 1

Unfortunately, there is no way to know what is triggering the Worksheet.Change event in this case. All you can know is that some change to the worksheet's contents has occurred and which cells within the worksheet has changed. You cannot know, however, what code was called to make the changes, or even if it was code that made the changes, as the user can cause the Worksheet.Change event to fire as well.

I see two issues with what you describe above:

1. You stated that you commenced the find-replace action with two cells selected. But the behavior that you describe suggests that you only had one cell selected. If you have two or more cells selected, then the find-replace action occurs only within the selected range. If you have only one cell selected, however, the find-replace occurs over all cells in the worksheet. So I strongly suspect that you had only one cell selected when you started the find-replace operation, not two.

2. The fact that the find text and the replace text overlap does not sound like a great idea, but it should not produce any problems with the kind of non-terminating recursion that you are describing. Is it possible that your worksheet_change() function that is handling the Worksheet.Change event is itself directly or indirectly invoking another find-replace action? If this is the case, then you very well could get the infinite recursion.

-- Mike