I have a verry complicated excel template to administrate, that has a large number of trigggers: Workbook_SheetDeactivate, Workbook_SheetActivate, Workbook_SheetSelectionChange, workbook_SheetChange

If the users use Find functionality and choose within Workbook - Find next, this will trigger allot of them, and will most likely freeze the file because of so many commands.

Is there any way to controll the triggers in case of using Find?

CodePudding user response：

When a user tries to find something (either by Ctrl F or clicking the "Find" button) the Cells.Find sub is run. The question then becomes, "Is it possible to run VBA code before executing user input?". I don't think that's possible.

If you could guarantee that your users would only search via pressing Ctrl F then you could make use of Application.OnKey. Effectively, you would rewrite the Cells.Find sub, but wrap it in Application.EnableEvents = False and Application.EnableEvents = True. That way, whenever your users pressed Ctrl F, events would be disabled, the search would occur, and then events would enable again.

CodePudding user response：

If there really is a danger of your file freezing then the first thing you need to consider is your code: you have coded all of your events at the Workbook level, rather than at the Worksheet level - this facilitates writing less code, but it does mean that the code is run more often....

You can't guarantee that a non-technical user will not use CTRL F (or your local equivalent) but if your file really does freeze if they use another method then they will learn not to repeat their mistake (given that CTRL F is standard across many Windows applications, it is reasonable to assume that it will be the standard method chosen by users).

In the Workbook_Open() event of your workbook you can use the OnKey method to trap the CTRL F (or your local equivalent) key combination to run a procedure which includes the lines below

Application.EnableEvents = False
Application.CommandBars("Edit").Controls("Find...").Execute

The problem then is that your events are disabled, as there isn't a way to detect when the user has finished using the Find window - I think you have 3 choices

• use the OnTime method to call a procedure to re-enable events after a fixed interval
• use DoEvents in a loop (effectively allowing the user to maintain some use of the user interface) until some specific action has occurred, e.g ActiveCell.Address is a defined value
• create a dedicated button somewhere (e.g. a non-modal UserForm) so that the user can click it to re-enable events