Disable buttons in custom ribbon before opening a vbModeless UserForm

Hello everyone,

I have a custom ribbon, in which one button triggers opening a UserForm, which is not modal.
Then I would like to disable certain other buttons in my ribbon before the UserForm is shown.

I have the following code:

Call ribbonRefresh
Call UserFormFoo.Show(vbModeless)

"ribbonRefresh" does call

ribbon.Invalidate

to update the ribbon. Also, I have the necessary "GetEnabled" sub in my ribbon handling module (this works as expected).
But the buttons in the ribbon do not get disabled when the code is run.

However, if I set a breakpoint in my "GetEnabled" sub the buttons get disabled.

Any help would be appreciated.
Thanks in advance.

BTW, it doesn't make any difference if the UserForm is opened with vbModeless or not.
However, if I comment out the part

Call UserFormFoo.Show(vbModeless)

the ribbon state is correctly updated, i.e. buttons are disabled.

Unfortunately, neither adding a DoEvents nor moving the ribbonRefresh to UserForm_Initialize or UserForm_Activate did the trick

BTW, it doesn't make any difference if the UserForm is opened with vbModeless or not.
However, if I comment out the part

Call UserFormFoo.Show(vbModeless)

the ribbon state is correctly updated, i.e. buttons are disabled.

I put a breakpoint in my ribbon's GetEnabled sub. And it seems it is simply not called at all.

Yes, GetEnabled is triggered if I close the form.

Set "ShowModal" property on "UserFormFoo" to "False". No Change

Originally Posted by OllieB

Ok. I tried, but I am out of ideas/options,unless you have set application.screenupdating=false somewhere

You need someone smarter than me to solve this problem.

I don't think so but I'll check again for application.screenupdating=false
Thanks for your efforts.

Originally Posted by OllieB

PS. also check for Application.EnableEvents= False when you are at it.

Ok, there's neither an Application.ScreenUpdating=False nor Application.EnableEvents=False anywhere in the code.
However, the problem seems to be that I start a kind of infinite loop after initializing the UserForm:

Do While
Loop

This is because I'm waiting for some events from an external system, whose result is to be displayed in the UserForm.

The code looks like this now:

Call UserFormFoo.Show(vbModeless) ' UserForm_Activate contains the call to ribbonRefresh, as well as Application.ScreenUpdating = True and DoEvents
Call UserFormFoo.startLoop        ' this is where the "infinite loop" starts

If I comment out the loop in startLoop, the ribbon gets updated as desired.

Any ideas how to improve this would be welcome.

Nope, didn't work.

Yeah, the loop already has a DoEvents and a Sleep...

BTW, I'm not a VBA expert and this loop seemed to be the only way to get what I wanted. If you have any suggestions for improvement, I'm all ears.

RibbonRefresh adds the button name to a collection. GetEnabled checks whether control.tag is in that collection.

The loop is now in its own sub in UserFormFoo.
Within the loop I ask a custom dll for some result values (the details of which I am not allowed to reveal). If a result exists, these values are then displayed in the UserForm.

the sub is called from the callback for the ribbon button that starts UserFormFoo.

Call UserFormFoo.Show(vbModeless)
Call UserFormFoo.startLoop

the dll call is synchronous

Originally Posted by OllieB

Could you try starting the loop via a button?

I tried just starting the loop from the button, with the same result: Ribbon state does not get updated.

As I said before, the problem must be somewhere within the loop because the ribbon state gets updated if I comment out the loop...

However, I'm so sick of stupid VBA now that I'm thinking of just leaving all buttons enabled and add a guard to their callbacks that checks if the UserFormFoo is currently running.
Not as nice but I hope that will work.

Originally Posted by JosephP

what was the code you used with the ontime method-were you still calling the startloop directly?

I tried:

Application.OnTime Now(), "theloop"

sub theloop()
    Call UserFormFoo.startLoop
end sub

Originally Posted by JosephP

did you try using ontime to call a routine that both shows the form and starts the loop? that way the button onAction callback can finish running before you load the form and start the loop

Holy ****. That seems to be working. Thanks a lot!