How do you clear event queue? 2

[MatthewGB]

Hi,

Is there any way to clear the event queue programmitically?
The reason I need to do this is that for some unknown reason, when running some code in an Excel spreadsheet, my Userforms get hidden. Even if I put in Userform1.show as the last statement in my code, the userform flashes up for a millisecond then closes down again.

I am thinking that there must be some other commands in a VBA event queue which are run even after the last line of my code is finished.

As you can probably tell, my question is a bit obscure, but if anyone understands what I am trying to get at, and is able to help, it would be greatly appreciated.

Thanks
Matt

 

[xlbo]

I think it's more likely that there is some code in your userform activate event that is shutting the form down... Rgds
~Geoff~

 

[MatthewGB]

I dont think thats the problem because the userforms stay visible only until i run a piece of OLEObject code on my worksheet. There isnt any code being used on the userform at this time to trigger the Userform activate event.

That is why I think there is some transparent VBA procedures being automatically processed in an event queue which is shutting down the userform. I have pretty much explored every other possibility.

Any ideas?

 

[RobBroekhuis]

Are these modeless userforms? After they disappear, can you check via the vbe debug window whether their "visible" property is set to true? There must be a way to troubleshoot this...
Rob

 

[MatthewGB]

Yes, they are modeless userforms.
I checked the "visible status" after the code is run and it says "true". However, immediately after this, the userform disappears. If i run the code again without making the userform visible again, the status returns false.

The funny thing is, there is no code (that i can tell), which runs between checking the visible status and the end of the procedure. That is why i think there may be some events or code that are run by VBA automatically, which are closing down this userform.

I have isolated the code which seems to be causing the problem. It is when i programmatically create a number of OLEObjects (checkboxes) onto the worksheet itself (not the userform).

If i run the code without creating the OLEObjects, the problem does not occur. Is there some VBA-generated code which runs in tandem with creating OLEObjects?

 

[RobBroekhuis]

Hi,
I reproduced your problem. Very disconcerting. Even when I unload the userform before the OLE object insertion, and try to show it again in vbModeless form right afterwards, it fails. I see it flash up, but it never even gets to the userform.activate event. Strangely, it works just fine if I redisplay it in vbModal form.
I do have a clue as to what's going on - I tried setting a breakpoint after the OLE insertion, and VBA wouldn't let me break ("Cannot break here&quot. The associated help told me it was because a programmatic change was made to the project using the extensibility (add-in) object model. I'm assuming this relates to having the VBProject changed by adding an OLE Object to the sheet object. Immediately after the macro ceases execution, I can redisplay the userform (modal or modeless). Somehow, VBA seems to be maintaining a flag saying certain events are off limits.
I do think I found a potential workaround: when the macro generating the OLE Object (and the sheet on which it is generated) are in a different workbook, the userform stays put. So if there is a way to separate the two, at least temporarily, you may have a solution.
Rob

 

[MatthewGB]

I think your conclusions about VBA maintaining some sort of flag is correct. I will have a play with your workaround to see if i can incorporate it into my program, but obviously the most desirable solution would be to remove the flag which is causing the problem. If/how I can do that is another matter entirely.

Thanks for your help in this matter Rob. I appreciate the time you put into helping me with this problem. If I manage to find another way to get around this problem, I will let you know.

 

[xlbo]

Rob - think you deserve a star for that - that's some pretty in depth help you've given there and opened my eyes to some potential problems in a project I'm working on at the mo.
Ta muchly Rgds
~Geoff~

 

[RobBroekhuis]

Hi all,
I found a workaround (funny what your mind does after turning in for the night). It's not pretty, but here goes:

..OLE Object code here..
Application.OnTime Now() + 0.5 / 3600 / 24, "RunMore"
End Sub

Sub RunMore()
UserForm1.Show vbModeless
End Sub

For some reason, relinquishing VBA's control over Excel releases whatever flag was set. So ending, only to start up again half a second later, does the trick. Unfortunately, the userform is actually unloaded during the OLE object creation, so you'll have to save whatever is on the form and put in back on afterwards. A pain, yes.
Rob

 

[MatthewGB]

Hi,

I will give that workaround a try today hopefully (time permitting).

I must say you've been very persistent in getting to the bottom of this problem. Your help is much appreciated.

I'll let you know how my program goes.

Thanks
Matt