Activate a particular Ribbon Tab

Question

I've seen lots of code for how to ActivateTab OnLoad but can't get it to work dynamically... on the click of a worksheet button for example.

There is a button on Sheet1 pointing at startHereConfigure

<customUI onLoad="RibbonOnLoad"
    xmlns="http://schemas.microsoft.com/office/2009/07/customui">

Public Rib As IRibbonUI
Sub RibbonOnLoad(ribbon As IRibbonUI)

    Set Rib = ribbon

End Sub

Sub startHereConfigure()

    Rib.ActivateTab "Configure"

End Sub

I want to be able to call startHereConfigure (and other similar subs) to bring to the fore a particular Tab - how do I do this?

Answer 1

Add this line to the custom XML file:

<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">

Where "RibbonOnLoad" is the name of the function to be called upon starting Outlook.

Add this funciotn into a standard module:

Public Rib As IRibbonUI

Sub RibbonOnLoad(ribbon As IRibbonUI)
   Set Rib = ribbon    
   Rib.ActivateTab "Configure" ' Name of the tab to activate    
End Sub

Answer 2

Jorge's answer is the normal way.

I introduce 2 other work-arounds here.

One is moving your custom tab control before the Home Tab by adding 'insertBeforeMso' in your customui(14).XML.

<tab id="id1" label="Tab1" insertBeforeMso="TabHome">

Your 'Tab1' is the first one, so it is activated first when opening your office file.

The other one is using SendKey method which is a little tricky. After adding onLoad macro in customui XML, you can add following lines in a module in VBE.

Sub onLoad(control As IRibbonUI)
    'control.ActivateTab "Tab1"
    Application.SendKeys "%Y%"   'Alt + Y + Alt key
End Sub

Here 'Y' key is your custom tab shortcut key and 'Alt' key is sent again after 'Y' key in order to prevent the Alt shortcut keys from appearing on ribbon menus.

Answer 3

Samuel Liew: I thought the way I answered was implicit enough. The following is the same expanded to be more explicit:

Sean: Unclear from the above if you got an answer or you solved it yourself. As, I can't see an answer and your last comment suggests you went off with false information.

Certainly your code should work in 2010 office systems. I'm doing just that and activating a range of different tabs, based on targeted worksheet 'types' becoming active. And the tab activation code is just part of a general workbook events handling sub (i.e. called by application event handlers). And that has been operational since prior to you asking this question.

So the response from Siddharth Rout above about not being able to do this is invalid.

I'm assuming your Tab id is defined in the xml to match the id in your code above per:

<tab id="Configure" label="YOUR LABEL" >

I'm also assuming the rest of your xml is correct, and you can see the tab, just not be able to activate it.

If all that's the case, as to 'How to': You should just need to call that sub. In my case I do that on Workbook or Worksheet activated (on open or change of window). But any call mechanic (such as your in-sheet control) should work just as well.

As an aside, I'd recommend using more obviously unique codes for ribbon control ids e.g. Using some leading chars from the overall function and the control type like 'xyzTab_Configure'. Just means if/when you add more, you have a explicit way to know which is which, and avoid any clashes.

That all covered: I have however run into an issue with office 2019. See: vba IRibbonUI.ActivateTab not working in Office 2019