Skip to main content

CustomUI

CustomUI implements embedded Excel ribbon with advanced customisation. It also overrides any existing ribbon entirely.

Features

Documentation

The following examples are based on CustomUI Sample to illustrate the concept.
See official Microsoft documentation for more details.

OnLoad

Initialise state of controls on load
<!-- XML -->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
onLoad="RibbonOnLoad">
Declare global variables and object
Public Ribbon As IRibbonUI
Public MySelectedTabTag As String
Public MySelectedGroupTag As String
Public MySelectedItemID As String
Initialise the ribbon
Sub RibbonOnLoad(Rib As IRibbonUI)

Set Ribbon = Rib
MySelectedTabTag = "tb1"
MySelectedGroupTag = "tb1gp2"
MySelectedItemID = "tb2gp2dd1_01"

End Sub

GetVisible

Generate visible attribute of tab control, dynamically
<!-- XML -->
<tab id="tb1"
tag="tb1"
label="Tab 1"
getVisible="ShowTab">
Set visible attribute of tab control based on the MySelectedTabTag
Sub ShowTab(control As IRibbonControl, ByRef visible)

If control.Tag Like MySelectedTabTag Then
visible = True
Else
visible = False
End If

End Sub
Generate visible attribute of group control, dynamically
<!-- XML -->
<group id="tb1gp3"
tag="tb1gp3"
label="Group 3"
getVisible="ShowGroup">
Set visible attribute of group control based on the MySelectedGroupTag
Sub ShowGroup(control As IRibbonControl, ByRef visible)

If control.Tag Like MySelectedGroupTag Then
visible = True
Else
visible = False
End If

End Sub

OnAction

Execute change of tab
<!-- XML -->
<button id="tb1gp1mn1_01"
label="Show Tab 2"
onAction="ChangeTab"
imageMso="ControlTabControl" />
Display ribbon tab on demand
' =====================================
' Tag:="testTab" Show/Hide only the Tab, Group or Control with Tag "testTab"
' Tag:="My*" Show/Hide every Tab, Group or Control with Tag that starts with "My"
' Tag:="*" Show/Hide every Tab, Group or Control
' Tag:="" Hide every Tab, Group or Control
' ======================================
Sub ChangeTab(control As IRibbonControl)

Select Case MySelectedTabTag
Case "tb1": Call RibbonRefresh(TabTag:="tb2", TabID:="tb2")
Case "tb2": Call RibbonRefresh(TabTag:="tb1", TabID:="tb1")
End Select

End Sub
Execute change of group
<!-- XML -->
<button id="tb1gp1mn1_02"
getLabel="LabelNextGroup"
onAction="ChangeGroup"
imageMso="FormControlGroupBox" />
Display tab group on demand
Sub ChangeGroup(control As IRibbonControl)

Select Case MySelectedGroupTag
Case "tb1gp2": Call RibbonRefresh(TabTag:=MySelectedTabTag, GroupTag:="tb1gp3")
Case "tb1gp3": Call RibbonRefresh(TabTag:=MySelectedTabTag, GroupTag:="tb1gp2")
End Select

End Sub
Refresh the ribbon
Sub RibbonRefresh(TabTag As String, Optional TabID As String, Optional GroupTag As String)

Application.ScreenUpdating = False

MySelectedTabTag = TabTag
If GroupTag <> "" Then
MySelectedGroupTag = GroupTag
End If

If Ribbon Is Nothing Then
MsgBox "Error, Save/Restart your workbook"
Else
Ribbon.Invalidate
If TabID <> "" Then Ribbon.ActivateTab TabID
End If

Application.ScreenUpdating = True

End Sub

GetSelectedItemID

Generate the default selected item of a dropdown control, dynamically
<dropDown id="tb2gp2dd1" 
label="Dropdown 1"
sizeString="WWWWWWWWWWW"
getSelectedItemID="GetDefaultItemID"
onAction="GetSelectedItemID">
Get default item to display by ID
Sub GetDefaultItemID(ByRef control As IRibbonControl, ByRef returnedVal As Variant)

returnedVal = MySelectedItemID

End Sub

GetLabel

Generate label attribute of control, dynamically
<!-- XML -->
<button id="tb1gp1mn1_02"
getLabel="LabelNextGroup"
onAction="ChangeGroup"
imageMso="FormControlGroupBox" />
Generate button label based on the opposite state to MySelectedGroupTag
Sub LabelNextGroup(control As IRibbonControl, ByRef returnedVal)

Select Case MySelectedGroupTag
Case "tb1gp2": returnedVal = "Group 3"
Case "tb1gp3": returnedVal = "Group 2"
End Select

End Sub
Get user selected item by ID
Sub GetSelectedItemID(control As IRibbonControl, ID As String, index As Integer)

MySelectedItemID = ID

End Sub