finding last running event

[foxguy]

I would like to run a sub (sub EndEvent()) at the end of every Event. But I don't want to run it twice.

When I change a value, both Worksheet_Change() and Worksheet_SelectionChange() run, so I can't put EndEvent in both Events.
And I believe there is some action that triggers 3 Events.

Is there some way to determine if there are any Events waiting to run? Or maybe a list of Events that run when different Events are triggered?

[MarvinP]

Hi foxguy,

On first thought I was thinking a well placed.

Please Login or Register  to view this content.

followed somewhere by turning them on again with

Please Login or Register  to view this content.

would do the trick.

Do you use these?

[foxguy]

HI Marvin;

I don't think that would work, but I'm open to the idea.

When I manually change a value in a cell, Worksheet_Change() starts. If I turn Events off in Worksheet_Change() it stops Worksheet_SelectionChange() from triggering, but I still want Worksheet_SelectionChange() to execute. I just want a sub to execute at the end of Worksheet_SelectionChange() and not execute in Worksheet_Change().

But if a macro changes the value in a cell, Worksheet_Change() executes, but Worksheet_SelectionChange() does not execute, so I would want the sub to execute at the end of Worksheet_Change().

So, somehow I need Worksheet_Change() to be able tell that a macro changed the value or the user changed the value, in order to decide whether to run my sub.

Do you see a different way to make it work?

[mikerickson]

How about a Public boolean variable RUNME.

Please Login or Register  to view this content.

If you set RUNME = True in the last event in the chain. Then, the next time some event triggers mySub, your sub will run only once, no matter where in the chain of events, the event codes start.

[MarvinP]

I've moved code from one event to the next to get what I want. When Change doesn't do it then SelectionChange might. I wonder if you've tried the event of Worksheet_Deactivate. I'd have to search to see what it really does.

[foxguy]

Hi Mike;

That's my problem. How do I determine what is the last Event in the Chain?

If I manually change a value Worksheet_Selectionchange() is the last Event, but if a macro changes a value then Worksheet_Change() is the the last Event.

If I had a list that showed the chain of Events for every action, then I could solve my problem.

[foxguy]

Hi Marvin;

I don't understand what you're trying to do. I'm familiar with all the Events. Each one is triggered by different actions. I want to run my sub at the end of all of them, but some actions trigger more than one Event, so I would like to run it only at the end of the last Event triggered.

[MarvinP]

Did you try the Worksheet_Deactivate Event?

I guess I'm not totally understanding the "at the end of the last event" part.
Do you want to fire this event only once? I guess yes.

It seems to me you need to make some decisions on what events will be triggered and then call this code at the last line of the Event triggered code.

[foxguy]

Hi Marvin;

You understand what I'm trying to do.

My problem is that some actions trigger 2 Events, and I think some actions can trigger 3 Events.

For example:
When I manually change a value it triggers 2 Events - Worksheet_Change() and Worksheet_SelectionChange(), so in that situation I want my sub to run at the end of Worksheet_SelectionChange().

But if a macro changes a value it only triggers 1 Event - Worksheet_Change(), so in that situation I want my sub to run at the end of Worksheet_Change().

Which means that Worksheet_Change needs to know whether a macro changed the value or the user changed the value.

I could easily solve this particular example, but I want to set up something that will automatically run my macro at the correct time no matter what action triggers any Event(s).

[MarvinP]

I think that Mike's suggestion above using Global or Public variables is your best bet for now. It seems you want to not trigger some events when a macro runs. And, these macros change cells in the worksheet, which will trigger the Event Macro. Turning off the EnableEvents so they don't trigger seems logical. It also seems like a lot of work to track down all possible paths.

[foxguy]

Hi Marvin;

I want ALL Events to execute when they are triggered.

Unless I'm missing something, turning off EnableEvents doesn't accomplish anything. It prevents the Events from executing, but I want all Events to execute. I just want a sub to run when the last triggered Event executes.

You're right, it's a lot of work to track down all possible chain of Events, which is why I'm looking for a way to have VBA figure out what the chain of Events is.

[romperstomper]

Can you explain why you want this in case there's an easier workaround? There is no accessible event stack that I am aware of.
Also, it is possible for a user to have set Excel not to change the active cell after pressing Enter.

[foxguy]

Hi Romper;

Here's part of what I'm doing.

I have menu buttons that will display the current status of EnableEvents, Activesheet.ProtectContents, ActiveWorkbook.Saved, etc.

I can click on the buttons to toggle EnableEvents on/off, Protect/UnProtect Activesheet, Save the Activeworkbook, etc.

So at the end of every Event chain I want to update the Captions of those buttons to reflect the underlying status. IOW, if an Event unprotected the ActiveSheet, I want the Protect button caption to change to "Sheet is UnProtected". When I change sheets, I want the button to reflect the status of the new sheet. When the workbook is not saved, the Not Saved button is enabled.

I am currently doing it at the end of every Event, and I just realized that it is doing it more than once because more than one Event is triggered sometimes. Don't know why I never noticed that before.

I discovered the duplicate execution because I'm modifying the sub to allow for workbook and worksheet specific buttons, and the time could become noticeable in the future, so I'm hoping to minimize the time now - before it becomes an issue.

I haven't tested this yet, but I'm now thinking of using a public variable and OnTime() to execute my sub 1 second after the 1st Event ends. Hopefully the 2nd Event in the chain will execute before the OnTime() is triggered and it will be able to tell that the OnTime() is already set (that's what the public variable is for), so it won't set it again. So when the last Event ends, it will allow the OnTime() to execute.

I don't like this idea, but it just might do what I want.

[romperstomper]

That's probably as good a way as any, though I'd be surprised if using each event added that much to the processing time. For the protection, it would be simpler to just add the built-in buttons to your toolbar - they should automatically reflect the appropriate state and display Protect or Unprotect sheet as required.

[foxguy]

Hi Romper;

The built in Protect button doesn't work like I want. When you click "Protect Sheet" it brings up the Protect Sheet dialogue. I want to skip that dialogue because I always use the same options for Protect. Although I might look into trying to use it but hijacking the OnAction for my own use. Don't know if that's possible, but it would be a better choice.

And obviously there is not a built-in button that will show me the EnableEvents status or ActiveWorkbook.Saved status, etc. So if I want those buttons, I might as well have a Protect Sheet button that works like I want.

It's not the Events that might add time, it's my sub that is run after all Events have executed. It might get cumbersome in the future, so while I'm working on it now I'm going to try and prevent it.

[romperstomper]

You can hook the Click event of any built in button and override its behaviour. That way you get the default captions and enablement that you want but can use your own code (and either cancel or leave the default action).

If all your code does is update a few buttons, I can't see it adding a lot of time. If it does more than that then yes you probably will need a timer.

[foxguy]

HI Romper;

I've never hooked the Click event of a built-in button. Can you point me in the right direction?

Now that I'm adding in Workbook and Worksheet specific buttons, I may have a Worksheet specific button do some kind of search or summation or who knows what. So I would rather deal with it now than wait until some future time when I might be on deadline.

[romperstomper]

Simple example for the Protect Sheet button:

Please Login or Register  to view this content.

[foxguy]

Thanks Romper