Customised Ribbon using Custom UI editor for Microsoft Office

[molesy01]

Hi. I have created a new ribbon using the editor and have created about 60 new tabs. Well as you imagine having all these tabs are a bit of nightmare scrolling along to find the tab i want. Does anyone know if there is a way of using a macro to search for a particular tab, maybe a combobox?
thanks if you can help.

 

[Domenic]

You can use the ActivateTab method of the Ribbon object to activate the desired tab. So, for example, let's say that the variable MyRibbon represents the Ribbon object, and that the control ID for the desired tab is "customTab". The following will activate the tab...

MyRibbon.ActivateTab "customTab"

Hope this helps!

 

[molesy01]

Hi Domenic
Sorry dont quite understand. Ive tried that but it failed. where would i actually put the code?
thanks

 

[Jaafar Tribak]

Here is an adaption of a code I wrote when I first installed Office 2016... Not sure if it will work with other office versions...This code is a vba alternative to using XML.

If the code works with your office version , it should list all your ribbon tabs on a userform listbox and you can activate the ribbon tabs by selecting their respective name from the listbox.

Here is a workbook example


Code goes on the UserForm:

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private oCol As New Collection


Private Sub UserForm_Initialize()

    Const CHILDID_SELF = 0&
    Dim vElement As Variant, oIaccList() As IAccessible
    
    oIaccList = GetRibbonTabsList
    
    For vElement = 0 To UBound(oIaccList)
        Me.ListBox1.AddItem oIaccList(vElement).accName(CHILDID_SELF)
        oCol.Add oIaccList(vElement)
    Next

End Sub


Private Sub ListBox1_Change()

    Call oCol(ListBox1.ListIndex + 1).accDoDefaultAction(0&)

End Sub


Private Function GetRibbonTabsList() As Variant

    Const CHILDID_SELF = 0&
    Const NAVDIR_FIRSTCHILD = &H7
    Const NAVDIR_NEXT = &H5
    Const STATE_SYSTEM_INVISIBLE = &H8000&
    Dim oIacc As Office.IAccessible, oIaccFilteredArray() As Office.IAccessible
    Dim i As Long, k As Long    
    
    Set oIacc = Application.CommandBars("Ribbon")
    
    For i = 0 To 6
        Set oIacc = oIacc.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
    Next i
    
    For i = 0 To 7
        Set oIacc = oIacc.accNavigate(NAVDIR_NEXT, CHILDID_SELF)
    Next i
    
    Set oIacc = oIacc.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
    
    ReDim oIaccChildrenArray(oIacc.accChildCount - 1)
    
    Call AccessibleChildren(oIacc, 0, oIacc.accChildCount - 1, oIaccChildrenArray(0), 1)
    
    For i = LBound(oIaccChildrenArray) To UBound(oIaccChildrenArray) - 1
        If ((oIaccChildrenArray(i).accState(CHILDID_SELF) And (STATE_SYSTEM_INVISIBLE)) = 0) Then
            ReDim Preserve oIaccFilteredArray(k)
            Set oIaccFilteredArray(k) = oIaccChildrenArray(i)
            k = k + 1
        End If
    Next i
    
    GetRibbonTabsList = oIaccFilteredArray
    
End Function

 

[molesy01]

Hi Jaafar
Thank-you for your reply. Unfortunately i am not that good in excel to understand your code. I have created a custom ribbon using the editor and it has too many tabs so i cannot see the tabs on the end. All i would like to do is have a combo box on sheet1 so i can select the tab i require.
thanks

 

[molesy01]

Hi Domenic
Thank-you for your reply. Unfortunately i am not that good in excel to understand your code. I have created a custom ribbon using the editor and it has too many tabs so i cannot see the tabs on the end. All i would like to do is have a combo box on sheet1 so i can select the tab i require.
thanks

 

[Jaafar Tribak]

Hi Jaafar
Thank-you for your reply. Unfortunately i am not that good in excel to understand your code. I have created a custom ribbon using the editor and it has too many tabs so i cannot see the tabs on the end. All i would like to do is have a combo box on sheet1 so i can select the tab i require.
thanks

Have you tried the workbook example in the link ?

 

[molesy01]

Hi
Sorry i didnt download the file, it works perfectly. your a star thank-you.

 

[Jaafar Tribak]

Hi
Sorry i didnt download the file, it works perfectly. your a star thank-you.

Glad you got this working.

Can I ask which version of office you are using ?

Regards.

 

[molesy01]

13