How does one activate ribbon components from an xlam file ?

[dschmitt]

I came across an Excel add-in (.xlam) that appears to use VBA script to activate Ribbon groups. I would like to know how this is done.

Illustrated in more detail but simplified. The xlam file creates a new Ribbon tab with one Ribbon group containing one button.
If I click on this button additional ribbon groups are added to the Ribbon tab.

A look in the VBA Editor VBA project list shows that no file was opened by clicking the Ribbon button.
The Ribbon xml file of this xlam only contains the script for the one Ribbon tab, Ribbon group and button.

I have the impression that the script activated by the button calls in the additional Ribbon groups and buttons.
And this seems to work without opening another xlam file. I have no idea how that is done.

Can somebody explain to me how to do that?

[Bob Phillips]

You cannot add to the ribbon on the fly using VBA, so I would guess that the other groups and buttons are setup in the xlam but not visible, and when the button is clicked their visible property is set and the ribbon is invalidated.

[dschmitt]

The ribbon xml file of the add-in has a comment at the top that says: <!-- The first part of the ribbon tab -->
And as I said earlier. This xml file does not contain the script for the 2 button groups which appear after clicking the button.
I know about visible and invisible. But that has not been used in this case.

[Bob Phillips]

Then I guess I do not know. What addin is it? Is it a COM addin created in VS?

[dschmitt]

xlam

[Paul_Hossler]

I came across an Excel add-in (.xlam) that appears to use VBA script to activate Ribbon groups. I would like to know how this is done.

Illustrated in more detail but simplified. The xlam file creates a new Ribbon tab with one Ribbon group containing one button.
If I click on this button additional ribbon groups are added to the Ribbon tab.

A look in the VBA Editor VBA project list shows that no file was opened by clicking the Ribbon button.
The Ribbon xml file of this xlam only contains the script for the one Ribbon tab, Ribbon group and button.

I have the impression that the script activated by the button calls in the additional Ribbon groups and buttons.
And this seems to work without opening another xlam file. I have no idea how that is done.

Can somebody explain to me how to do that?


I believe that the XLAM has the Fluent XML in it for all the groups, but with only one Group visible; the other groups are also there but Not visible. It doesn't really 'create' a new tab, but has one itself

Clicking the button changes a status variable and then Invalidates the ribbon.

The getVisible callback for the groups then makes the second etc. groups visible

I can make up a small example if your want.

[dschmitt]

Paul, if you could give me an example that would be great. I have programmed quite a few Ribbon (xml) files. That is for all the add-ins I created for myself and at work. But these Ribbon files are relative simple. I just created tabs with button groups, pull down menus and checkboxes. I never used visible/invisible. But having said that, it is easy for me to understand the concept of visible/invisible. I read online and saw some example scripts.

Based on what I learned online I looked in the ribbon xml file in question and looked for visible/invisible code and especially for the 2 "missing" pulldown menus and their buttons. My current understanding is that the code for the 2 pulldown menus with their buttons and onaction specifications must be there including a statement for visible/invisible. But that code is not there.

Perhaps I don't understand enough yet, therefore I would appreciate, if you could provide me with an example that I could test to get a better understanding of the code.

[Paul_Hossler]

This is in the attached XLSM, but I saved it as an XLAM and it seems to work OK




<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad" >
<ribbon>
<tabs>
<tab id="CustomTab" label="My Tab">
<group id="gMaster" label="Master Group">
<toggleButton id="tbA"
size="large"
imageMso="A"
getLabel="getToggleLabel"
getPressed="getTogglePressed"
onAction="onToggleAction"
screentip = "This hides or shows Group A"
/>
<toggleButton id="tbB"
size="large"
imageMso="B"
getLabel="getToggleLabel"
getPressed="getTogglePressed"
onAction="onToggleAction"
screentip = "This hides or shows Group B"
/>
<toggleButton id="tbC"
size="large"
imageMso="C"
getLabel="getToggleLabel"
getPressed="getTogglePressed"
onAction="onToggleAction"
screentip = "This hides or shows Group C"
/>
</group>

<group id="gA" label="Group A" getVisible = "getGroupVisible">
<button id="bA1" label="FirstA" size="large" onAction="actionButton" imageMso="HappyFace"/>
<button id="bA2" label="SecondA" size="large" onAction="actionButton" imageMso="HappyFace" />
<button id="bA3" label="ThirdA" size="large" onAction="actionButton" imageMso="HappyFace" />
</group >
<group id="gB" label="Group B" getVisible = "getGroupVisible">
<button id="bB1" label="FirstB" size="large" onAction="actionButton" imageMso="HappyFace"/>
<button id="bB2" label="SecondB" size="large" onAction="actionButton" imageMso="HappyFace"/>
<button id="bB3" label="ThirdB" size="large" onAction="actionButton" imageMso="HappyFace"/>
</group >
<group id="gC" label="Group C" getVisible = "getGroupVisible">
<button id="bC1" label="FirstC" size="large" onAction="actionButton" imageMso="HappyFace"/>
<button id="bC2" label="SecondC" size="large" onAction="actionButton" imageMso="HappyFace"/>
<button id="bC3" label="ThirdC" size="large" onAction="actionButton" imageMso="HappyFace"/>
</group >
</tab>
</tabs>
</ribbon>
</customUI>




and




Option Explicit
Public bVisibleA As Boolean, bVisibleB As Boolean, bVisibleC As Boolean
Public bPressedA As Boolean, bPressedB As Boolean, bPressedC As Boolean
Public oRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub OnRibbonLoad(ribbon As IRibbonUI)
Set oRibbon = ribbon

bVisibleA = False
bVisibleB = False
bVisibleC = False
bPressedA = False
bPressedB = False
bPressedC = False

End Sub
'Callback for tbA getLabel
Sub getToggleLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "tbA"
If bVisibleA Then
returnedVal = "Hide A"
Else
returnedVal = "Show A"
End If
Case "tbB"
If bVisibleB Then
returnedVal = "Hide B"
Else
returnedVal = "Show B"
End If
Case "tbC"
If bVisibleC Then
returnedVal = "Hide C"
Else
returnedVal = "Show C"
End If
End Select
End Sub
'Callback for tbA getPressed
Sub getTogglePressed(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "tbA"
returnedVal = bPressedA
Case "tbB"
returnedVal = bPressedB
Case "tbC"
returnedVal = bPressedC
End Select
End Sub
'Callback for tbA onAction
Sub onToggleAction(control As IRibbonControl, pressed As Boolean)

Select Case control.ID
Case "tbA"
bPressedA = Not bPressedA
bVisibleA = Not bVisibleA
oRibbon.Invalidate
Case "tbB"
bPressedB = Not bPressedB
bVisibleB = Not bVisibleB
oRibbon.Invalidate
Case "tbC"
bPressedC = Not bPressedC
bVisibleC = Not bVisibleC
oRibbon.Invalidate
End Select
End Sub
'Callback for gA getVisible
Sub getGroupVisible(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "gA"
returnedVal = bVisibleA
Case "gB"
returnedVal = bVisibleB
Case "gC"
returnedVal = bVisibleC
End Select
End Sub
'Callback for bA1 onAction
Sub actionButton(control As IRibbonControl)
MsgBox "You clicked " & control.ID
End Sub

[dschmitt]

Paul, very nice example! I understand how the scripts work. I will definitely keep this example as a template for my future work.
Regarding my question, your xml script confirmed that the groups whose visibility toggle need to be marked with a getVisible statement in the xml file.
And, importantly, the scripts for those visibility toggling ribbon elements are in the xml file. But in the xml file in question I don't see the script for these visibility toggling ribbon elements.
I am now at home and it is weekend. I don't have the xml script with me. So, I can't show it to you now. I will get back to you early next week.

[Bob Phillips]

Paul's suggestion is exactly the same as the one I made, but I referred to buttons not togglebuttons (and I made a demo with such). I didn't bother showing you my code because you said you knew that approach.

But as you said, your addin doesn't have those hidden controls, which is why I asked what addin it was. Showing us the XML is pointless unless we can see the addin and its code as well.

[Bob Phillips]

BTW, just for info, this is the code I created


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

<ribbon>
<tabs>
<tab id="tabDemo" label="Demo Ribbon">

<group id="grpDemoGroup1"
label="Initial Group"
visible="true">

<button id="btnDemoShowRest"
onAction="rxDemoShowRest"
label="Show Rest"
imageMso="FileWorkflowTasks"
size="large"
visible="true"
enabled="true" />
</group>

<group id="grpDemoGroup2"
label="Now You See Me"
getVisible="rxDemoGetVisible">

<button id="btnDemoButton1"
onAction="rxDemoButton1"
label="New Button 1"
imageMso="ReviewProtectWorkbook"
size="large"
visible="true"
enabled="true" />

<button id="btnDemoButton2"
onAction="rxDemoButton2"
label="New Button 2"
imageMso="StartTimer"
size="large"
visible="true"
enabled="true" />

<button id="btnDemoButton3"
onAction="rxDemoButton3"
label="New Button 3"
imageMso="FileSaveAsPowerPointPpsx"
size="large"
visible="true"
enabled="true" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>


Private Sub Workbook_Open()
flgShowRest = False
End Sub



Global flgShowRest As Boolean
Global rxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)

Set rxDemoIRibbonUI = ribbon

If Val(Application.Version) >= 14 Then rxDemoIRibbonUI.ActivateTab "tabDemo"
End Function

Public Function rxDemoGetVisible(control As IRibbonControl, ByRef Visible)

Select Case control.ID

Case "grpDemoGroup2": Visible = flgShowRest
End Select
End Function

Public Function rxDemoShowRest(control As IRibbonControl)
flgShowRest = Not flgShowRest
rxDemoIRibbonUI.Invalidate
End Function

[Paul_Hossler]

@XLD --

I had done something earlier with toggle buttons and the on/off feedback to the user seemed to be a little more better than buttons. So that's why I threw it in that way



@dschmitt

1. When you click one of the toggle buttons this fires which flips the status variables AND invalidates the ribbon ( = re-init status, etc.)



Sub onToggleAction(control As IRibbonControl, pressed As Boolean)

Select Case control.ID
Case "tbA"
bPressedA = Not bPressedA
bVisibleA = Not bVisibleA
oRibbon.Invalidate
Case "tbB"
bPressedB = Not bPressedB
bVisibleB = Not bVisibleB
oRibbon.Invalidate
Case "tbC"
bPressedC = Not bPressedC
bVisibleC = Not bVisibleC
oRibbon.Invalidate
End Select
End Sub


[/CODE]


2. As part of the ribbon re-initing, the 3 group getVisible callbacks each ask if the the group is to be displayed or not



'Callback for gA getVisible
Sub getGroupVisible(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "gA"
returnedVal = bVisibleA
Case "gB"
returnedVal = bVisibleB
Case "gC"
returnedVal = bVisibleC
End Select
End Sub


There are many ways to clean the Mark I version code, but I went for a straight forward approach and didn't try to be too clever :rotlaugh: since that's when I get myself in trouble

[Paul_Hossler]

@xld -- nice example. I forgot about ActivateTab


This is the callback module a little more cleaned up

One thing I REALLY don't like (philosophically) is having to use global variables to track status, but I've never found a way to avoid them :banghead:




Option Explicit
Public bPressedA As Boolean, bPressedB As Boolean, bPressedC As Boolean
Public oRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub OnRibbonLoad(ribbon As IRibbonUI)
Set oRibbon = ribbon
End Sub
'Callback for tbA getLabel
Sub getToggleLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "tbA"
returnedVal = IIf(bPressedA, "Hide A", "Show A")
Case "tbB"
returnedVal = IIf(bPressedB, "Hide B", "Show B")
Case "tbC"
returnedVal = IIf(bPressedC, "Hide C", "Show C")
End Select
End Sub
'Callback for tbA getPressed
Sub getTogglePressed(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "tbA"
returnedVal = bPressedA
Case "tbB"
returnedVal = bPressedB
Case "tbC"
returnedVal = bPressedC
End Select
End Sub
'Callback for tbA onAction
Sub onToggleAction(control As IRibbonControl, pressed As Boolean)
Select Case control.ID
Case "tbA"
bPressedA = pressed
oRibbon.Invalidate
Case "tbB"
bPressedB = pressed
oRibbon.Invalidate
Case "tbC"
bPressedC = pressed
oRibbon.Invalidate
End Select
End Sub
'Callback for gA getVisible
Sub getGroupVisible(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "gA"
returnedVal = bPressedA
Case "gB"
returnedVal = bPressedB
Case "gC"
returnedVal = bPressedC
End Select
End Sub
'Callback for bA1 onAction
Sub actionButton(control As IRibbonControl)
MsgBox "You clicked Button = " & control.ID
End Sub

[Bob Phillips]

I had done something earlier with toggle buttons and the on/off feedback to the user seemed to be a little more better than buttons. So that's why I threw it in that way

Wasn't knocking it Paul, just remarking on what I used. I must admit ToggleButtons never come to mind for me for some reason.


One thing I REALLY don't like (philosophically) is having to use global variables to track status, but I've never found a way to avoid them

You could maybe try a static variable, although that of course restricts you to amending it from just one place (or create a globals class :)).

[Paul_Hossler]

Wasn't knocking it Paul, just remarking on what I used. I must admit ToggleButtons never come to mind for me for some reason..

Didn't take it that way.

OP said Buttons in #1, but what fun would that be :devil2:

I (out of habit) use ToggleButtons and change the Label and sometimes the icon for things like hide/show

[dschmitt]

I can't give you the xlam ribbon script because access to the script is locked. But, I think, a look at the screenshots of the ribbon and a look at the xml file should be sufficient to illustrate to you what I was trying to explain.

I took screenshots of the Ribbon tab before and after clicking the "Connect" button (see attached jpg file).
Clicking the "Connect" button starts the login procedure. With successful login the pulldown menus "Calculator" and "Finder" appear in the "Protocols" button groups.

Below is the xml script. The key button group to look at is, I believe, the "Protocols" button group. To make the xml script shorter I removed 4 lines of dynamic menus in the "Protocols" button group.
Question, where in the button group "Protocols" is the script for the pulldown menus "Calculator" and "Finder"?


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad"><ribbon>
<tabs>


<!-- The first half of the ribbon tab -->


<tab id="ABCTab" getLabel="DisplayName" insertAfterMso="TabHome" getVisible="IsActive">


<group id="ServerGroup" label="Server">


<splitButton id="ConnectSB" showLabel="true" size="large">


<button id="ConnectABC” imageMso="ServerConnection" onAction="ConnectToServer" label="Connect"/>

<menu id="ConnectMenu" enabled="true" label="Connection">
<button id="Connect" imageMso="ServerConnection" onAction="ConnectToServer" label="Connect to Server" getSupertip="ConnectToServerTip"/>
<button id="ChangeServer" image="icnChangeServer" onAction="ChangeServer" label="Change Server" getSupertip="ChangeServerTip"/>
</menu>


</splitButton>


</group>


<group id="Menus" label="Protocols">


<dynamicMenu id="MenuABC” getEnabled="HaveABCProtocols" label="Recently Used" screentip="Recently used protocols" image="icnFolderLarge" size="large" getContent="ABCMenuContent"/>

<dynamicMenu id="Menu1" tag="0" getVisible="HaveFolderTag" getLabel="FolderTag" getSupertip="FolderTipTag" image="icnFolderLarge" size="large" getContent="MenuContentTag"/>
<dynamicMenu id="Menu2" tag="1" getVisible="HaveFolderTag" getLabel="FolderTag" getSupertip="FolderTipTag" image="icnFolderLarge" size="large" getContent="MenuContentTag"/>

<button id="OpenProtocolSearch" image="icnProtocolSearchLarge" size="large" onAction="OpenProtocolSearch" label="Protocol Search" getEnabled="HaveServerConnection"/>


</group>


<group id="ABProtocols" label=“Favorites">


<button id="OpenManageFavorites" getEnabled="HaveServerConnection" imageMso="AddToFavorites" onAction="OpenManageFavorites" label="Manage Favorites"/>

<button id="ABProtocol1" image="icnProtocol" onAction="RunABProtocolTag" tag="0" getVisible="HaveABProtocolTag" getLabel="ABProtocolNameTag" getSupertip="ABProtocolCommentTag"/>
<button id="ABProtocol2" image="icnProtocol" onAction="RunABProtocolTag" tag="1" getVisible="HaveABProtocolTag" getLabel="ABProtocolNameTag" getSupertip="ABProtocolCommentTag"/>


<dynamicMenu id="ABMoreMenu" tag="6" getVisible="HaveMoreABProtocols" label="More" image="icnFolder" getContent="ABMenuContent"/>


</group>


</tab>
</tabs>
</ribbon>
</customUI>

[Bob Phillips]

As we said, you have the controls just not visible. They are menus not buttons, but they both have the getLabel callback that sets the name. There be no dragons there.

[Paul_Hossler]

Question, where in the button group "Protocols" is the script for the pulldown menus "Calculator" and "Finder"?





<dynamicMenu id="Menu1" tag="0" getVisible="HaveFolderTag" getLabel="FolderTag" getSupertip="FolderTipTag" image="icnFolderLarge" size="large" getContent="MenuContentTag"/>
<dynamicMenu id="Menu2" tag="1" getVisible="HaveFolderTag" getLabel="FolderTag" getSupertip="FolderTipTag" image="icnFolderLarge" size="large" getContent="MenuContentTag"/>



It appears that there is a 'HaveFolderTag' callback in the locked XLAM that determines if the menu is visible or not. The getVisible callback works just like the ones we've shown in the examples

Edit the XML to just delete the getVisible="HaveFolderTag", save it and see. The two missing ones will most likely be there all the time (or at least that's my guess)

[dschmitt]

I boiled down the xml script to the bare bones. The script below just has the login button and the 2 dynamic getVisible menus. I tested it. It is functional.
Paul, in your example all the ribbon elements are in the xml file and you use the xlam script to control the visible/invisible toggle.
In my example the xml does not contain all the ribbon elements. That means the xlam script does not only control the toggle but also contains the script for the to be toggled ribbon elements.
Can you give me a simple xml/xlam script example with one toggling dynamic menu that reproduces my example?



<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="RibbonLoad">
<ribbon>
<tabs>

<tab id="ABCTab" getLabel="RibbonDisplayName" insertAfterMso="TabHome" getVisible="RibbonIsActive">

<group id="ABCServerGroup" label="Server">

<button id="ABCConnect" imageMso="ServerConnection" onAction="ConnectToServer" label="Connect"/>

</group>

<group id="ABCMenus" label="Protocols">

<dynamicMenu id="ABCMenu1" tag="0" getVisible="HaveFolderTag" getLabel="FolderByTag" image="icnFolderLarge" size="large" getContent="MenuContent"/>
<dynamicMenu id="ABCMenu2" tag="1" getVisible="HaveFolderTag" getLabel="FolderByTag" image="icnFolderLarge" size="large" getContent="MenuContent"/>

</group>

</tab>
</tabs>
</ribbon>
</customUI>

[dschmitt]

Paul, if you are creating an example, please populate the getVisible dynamic menu with a couple buttons that activate a simple script like the Msgbox window in your example.

[Bob Phillips]

You keep saying that your script doesn't have all of the ribbon elements, and then you show us script with the elements in it.We are going round in circles.

[dschmitt]

xld, I think we are talking past each other. The line of xml script below makes visible the pulldown menu "Calculators". So, from your perspective, yes, everything is there to make the dynamic menu visible. That is true.


<dynamicMenu id="ABCMenu1" tag="0" getVisible="HaveFolderTag" getLabel="FolderByTag" image="icnFolderLarge" size="large" getContent="MenuContent"/>

I am asking: where is the script for the content of that dynamic menu. That pulldown menu contains several buttons. Each of which call a particular calculation routine. There has to be somewhere script for these buttons.

[Paul_Hossler]

Without seeing any code, the menu items are probably returned with this callback



getContent="MenuContent"

[Paul_Hossler]

I think your addin's dynamic menus' getContent callback requires constructing a XML string on the fly

Here's a little example (all 4 menus are the same just 'cause I was lazy) but show how I constructed the XML

On the WS Menu, Col A is the menu item label, Col B is the image, and Col C is the OnAction callback to call

BTW, I probably 'borrowed' 90% from someone on the web, but don't remember who or where so apologies to the original source. I just wrapped it to fit my style




Option Explicit
'Callback for GetContent to return XML used to create dynamicMenu
Sub GetContent(control As IRibbonControl, ByRef content)
Dim sXML As String

Dim rMenus As Range
Dim i As Long
Dim arrLabels() As String
Dim arrImages() As String
Dim arrProcedures() As String

Set rMenus = Worksheets("Menu").Cells(1, 1).CurrentRegion
ReDim arrLabels(0 To rMenus.Rows.Count - 1)
ReDim arrImages(0 To rMenus.Cells.Count - 1)
ReDim arrProcedures(0 To rMenus.Rows.Count - 1)

'these are all the same for demo
For i = LBound(arrProcedures) To UBound(arrProcedures)
arrLabels(i) = rMenus(i + 1, 1).Value
arrImages(i) = rMenus(i + 1, 2).Value
arrProcedures(i) = rMenus(i + 1, 3).Value
Next i

'Open the XML string
Select Case control.ID
Case "mMenu1"
content = XMLDynMenuEntry("mMenu1", arrLabels, arrImages, arrProcedures)
Case "mMenu2"
content = XMLDynMenuEntry("mMenu2", arrLabels, arrImages, arrProcedures)
Case "mMenu3"
content = XMLDynMenuEntry("mMenu3", arrLabels, arrImages, arrProcedures)
Case "mMenu4"
content = XMLDynMenuEntry("mMenu4", arrLabels, arrImages, arrProcedures)
Case Else
'Do Nothing
End Select
End Sub


Private Function XMLDynMenuEntry(sButton As String, aLabel As Variant, aImage As Variant, aProc As Variant) As String
Dim sQ As String, s As String
Dim i As Long
sQ = Chr(34)


s = "<menu xmlns="
s = s & sQ & "http://schemas.microsoft.com/office/2006/01/customui" & sQ
s = s & " itemSize=" & sQ & "normal" & sQ & ">" & vbCrLf
For i = LBound(aLabel) To UBound(aLabel)
s = s & "<button"
s = s & " id=" & sQ & sButton & (i + 1) & sQ
s = s & " label=" & sQ + (aLabel(i)) + sQ
s = s & " imageMso=" & sQ + (aImage(i)) + sQ
s = s & " onAction=" & sQ & (aProc(i)) & sQ
s = s & "/>" & vbCrLf
Next i

s = s & "</menu>"
XMLDynMenuEntry = s
End Function

[dschmitt]

Paul, great. Thank you. I got the concept. Again, I will keep your script as a template in case I need it.
I learned quite a bit in this thread!