Access RibbonUI object from Application?

[donh]

Is it possible to navigate to the IRibbonUI object for Excel from the
Application object? I know that I can get addressability to the Ribbon UI
object with on onLoad callback, but saving that in a global variable in VBA
is problematic: If any macro ever encounters an error, VBA clears all the
global variables. I'd like to be able to get back reliably to my customized
part of the Ribbon from my code, later, to do things like enable/disable
buttons, and to "invalidate" it so that it rebuilds the dynamic menus.

For the "old" code I have, using Menus, I've been able to get to the menus
from the CommandBars property of the Application. But I don't see any way to
navigate to the Ribbon stuff. I've searched MSDN and various forums, without
finding an answer.

Thanks in advance for any tips or pointers.

 

[Ron de Bruin]

Hi donh

See the example file on this page
http://www.rondebruin.nl/ribbon.htm

Point 3, example 1

 

[donh]

Hi Ron,
Thank you, it's kind of you to help, but I had already looked at that code,
and unfortunately it doesn't answer my question. The example code uses the
module-level variable 'Rib" to access the ribbon, having set that variable in
the onLoad callback. Sometimes things go wrong in macros (yes, I know, they
shouldn't...but they do) and if there is any error, even in a macro that has
nothing to do with ribbons, the variable Rib gets set back to Nothing, at
which point there's no way to find the Ribbon objects again.

My question is whether there is any independent way for VB code to navigate
to the Ribbon objects, perhaps starting with the Application object (or any
other always-accessible place)? I would like my module to be essentially
"stateless" and not depend on that global variable remaining set. In the
pre-ribbon days, one could navigate from the Application.CommandBars property
and find all the menu objects. This doesn't appear to work the same way for
ribbons, and I'm looking for the comparable technique.

 

[Ron de Bruin]

Hi Don H.

I will try a few things this weekend to avoid this problem