Disabling / Enabling "Upstream" Event Triggering

[bigfatroundguy]

I use event code within the worksheet, workbook and application classes and I understand that there are events that will trigger responses within all 3 classes - an example would be a range selection change ("SelectionChange" within a worksheet object, "SheetSelectionChange" within a workbook or application object). I further understand the order of events is from least significant object to the most significant object (so the worksheet event code will go first, followed by the workbook event code and finally the application event code). Finally, I believe that I am on the right path in trying to leverage the "Application.EnableEvents" property (set it to false to stop all responses to event triggering and back to true when you want the code to pay attention to event triggering). It's my belief that I should be able to trap and respond to an event at the worksheet level, and stop the event from being trapped and responded to at the workbook and/or application level.

So far, I have been unsuccessful. I have attached 2 example workbooks that contain the bones of the code with which I'm tinkering - Test-Event-Enable-1 puts event code in the worksheet code in the sheet module, workbook code in ThisWorkbook and application code in a class module; Test-Event-Enable-2 puts event code for all objects into class modues. SetupEvents / SetupAppEvents must be executed to turn the turn event handling on and I simply incorporate msgbox code to reflect what event code module is being executed when I select different ranges on a worksheet.

Would anyone have any advice / suggestions (if this is not possible, I'd even like to know that). Many thx, BFRG

[romperstomper]

There is, to my knowledge, no built-in way of doing that. If you have control over all three sinks, then you could create a method of doing it, but otherwise I think you are out of luck.
Can I ask what the purpose is?

[JBeaucaire]

I think this is a case of "too late." You have two selectionchange events active and both have already triggered. All the Application.EnableEvents = False accomplishes is keeping new events from triggering as a result of what the macro itself does next.

[romperstomper]

I further understand the order of events is from least significant object to the most significant object (so the worksheet event code will go first, followed by the workbook event code and finally the application event code)

Do you, as a matter of interest, have a reference for this? I have never seen this documented anywhere and therefore, while it appears to be true in my experience, I would not like to rely on it.

[JBeaucaire]

It occurred to me that you CAN define controlling variables to determine whether each SelectionChange macro should run its code or not. You can use a GLOBAL variable to pass information around to make this work.

1) Declare global variable - put this in a regular module (I added to your modclsAppEvent, but could go in Module1, Module2, etc)

Please Login or Register  to view this content.

2) Define worksheet event to flip this new variable - I've decided to suppress the ws_sc event if the selection is in column 4.

Please Login or Register  to view this content.

This means you should only see the ws_sc messagebox for all columns other than "D".



3) Define workbook macro to flip the variable, too - this macro sets the flag back, only runs its code when the flag is set to allow it.

Please Login or Register  to view this content.

This code would only run if the column were "D" and the ws_sc had not set the variable to TRUE.

[bigfatroundguy]

Thank you all for your posts.

Romperstomper - my reference is Chip Pearson's website on "Events And Event Procedures In VBA" http://cpearson.com/excel/Events.aspx. I have sent him a note seeking clarification as well. I am working with multiple worksheets in which i would like to execute common code but there will be some worksheets that will require specific code so, I would like to build a workbook module for most of the worksheets and where i have something worksheet specfic, then I will include code in the sheet itself.

JBeaucaire - you suggestion was right on. I have been noddling on it during my business travels last week and your suggestion just distilled in my mind. In my modclsAppEvent module, I declare and initialize a boolean variable called HBS_SelectionChange_Event. At the worksheet level, I set the state of the HBS_SelectionChange_Event (true - address the event at the worksheet level only, false - address the event beyond the worksheet level). At the workbook level, I test the state of HBS (false - address the event at the workbook level THEN set it to true, true - the event was addressed at the worksheet level). At the application level, I test the state of HBS (false - address the event at the application level, true - the event was addressed at a prior level, then I reset the variable back to false).

I have attached the coding I settled upon.

Many thx, BFRG

[romperstomper]

That page is certainly wrong about EnableEvents (rather than EnabledEvents) and whilst, as I said, the event order ties in with what I have seen, I have still never seen any official MS documentation to that effect.

[JBeaucaire]

Glad we were able to inspire a solution for you. Go team!

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.