It takes two?

[RetiredGeek]

Hi All,

I found this neat code that will let you use VBA to switch the Tab on the ribbon. The code works great, well almost.
If I call it from the Auto_Open() procedure in an Excel file it does not work. However, if I then press F8 and rerun the Auto_Open() procedure it does exactly what it is supposed to do, .i.e. select the Add-Ins tab. I've tried everything I can think of to insert delays into the process hoping that would do the trick but no luck. Any Ideas.

Update: I just had a thought, amazing I know, but I was running this in compatibility mode, e.g. file type .xls. So I saved a copy as a .xlsm file and viola it works as it should. Now the only question is why when in compatibility mode does it work on the second run?

[Gary Frieder]

Hi RG,

I don't know the answer to your question, but thanks for pointing to this interesting topic!

If anyone's interested, here's a link to the "Accessing the Ribbon with VBA" article by Tony Jollans. There's also a downloadable sample on that page.

Gary

[RetiredGeek]

I just love computers.

This morning the code will not work on the initial load no matter what I do it only runs correctly on the second invocation. I'm getting balder by the minute.

[rory]

Re the AutoOpen:

   If CInt(Application.Version) > 11 Then SendKeys "%(x)", True

Surely this should be <= 11 and not > 11?

   If Application.Version = "14.0" Then SwitchTab "Add-Ins"

For 2010, you do not need any of this code anyway as you can activate a tab directly in VBA, as long as you have obtained a reference to the IRibbonUI class in the onLoad callback.

[RetiredGeek]

Rory,

Actually, they are both designed to do the same thing, I forgot to take out the earlier one which I put in with Office 2007 Ver 12.

For 2010, you do not need any of this code anyway as you can activate a tab directly in VBA, as long as you have obtained a reference to the IRibbonUI class in the onLoad callback.

Playing the noob here just how do I do that. I haven't a clue about the ribbon interface yet, I just finished figuring how to do everything I wanted with custom menus in 2003.

[rory]

See Ron's site here.

[RetiredGeek]

Rory,

Thanks for the link. From a quick read it looks like it will take a while to digest. Thanks again.

[rory]

It is well worth a read. Once you get the ideas, the Ribbon begins to actually make a lot of sense from a developer point of view.