The Fluent Ribbon - done right. A generic dispatcher greatly easing Ribbon development in both .NET & VBA
Tied up in knots programming the Fluent Ribbon? Wondering why you bought an MS-Office with no wiring at the front desk? Here is an example of how the Fluent Ribbon was meant to be delivered.
This solution uses generic callbacks eponymous with their XM tags, with a dictionary lookup on each callback invocation, to automatically route each to its appropriate control. This implementation provides not just a pre-programmed ViewModel for all one's Ribbon controls, but a Model template as well.
The supplied Add-In also comes with a built in suite of Ribbon controls that can be dynamically attached to:
- 1 ToggleButton;
- 3 CheckBoxes;
- 3 DropDowns; and
- 3 (Action)Buttons.
The code to activate one of the (Action)Buttons, with Click event handler programmed in VBA and custom text defined at run time, is as simple as this, in three modules:
RibbonModel, declaring, initializing and handling the events from the controls used:
Option Explicit
Private CustomGroup As IGroupModel
Private WithEvents Button1 As ButtonModel
Attribute Button1.VB_VarHelpID = -1
Private Sub Button1_Clicked(ByVal control As IRibbonControl)
ButtonProcessing.Button1_Processing
End Sub
Private Sub Class_Initialize()
Dim Strings As IControlStrings
With ThisWorkbook.ModelServer
Set CustomGroup = .GetGroupModel("CustomizableGroup")
Set Button1 = .GetButtonModel("CustomizableButton1") _
.SetImage(.NewImageObjectMso("MacroPlay"))
End With
End Sub
ResourceLoader, supplying string and image resources to the dispatcher:
Option Explicit
Implements IResourceLoader
''' <summary>Serving Button, ToggleButton, CheckBox, Menu, and Gallery controls. </summary>
Private Function IResourceLoader_GetControlStrings2(ByVal ControlId As String) As IControlStrings2
Select Case (ControlId)
Case "CustomizableButton1":
Set IResourceLoader_GetControlStrings2 = ThisWorkbook.ModelServer.NewControlStrings2( _
Label:="This is cool!", _
ScreenTip:="VBA-Customized Button Screentip", _
SuperTip:="This button is completely" & vbNewLine & _
"customized within the VBA" & vbNewLine & _
"workbook.", KeyTip:="", Description:="")
Case Else:
Set IResourceLoader_GetControlStrings2 = Nothing
End Select
End Function
''' <summary>Serving all other controls. </summary>
Private Function IResourceLoader_GetControlStrings(ByVal ControlId As String) As IControlStrings
Select Case (ControlId)
Case "CustomizableGroup":
Set IResourceLoader_GetControlStrings = ThisWorkbook.ModelServer.NewControlStrings( _
Label:="VBA Custom Controls", _
ScreenTip:="", SuperTip:="", KeyTip:="")
Case Else:
Set IResourceLoader_GetControlStrings = Nothing
End Select
End Function
Private Function IResourceLoader_GetImage(ByVal Name As String) As Variant
IResourceLoader_GetImage = "MacroSecurity"
End Function
and ThisWorkbook, initializng the connection to the dispatcher.
Option Explicit
Private Const COMAddInName As String = "PGSolutions.BetterRibbon"
Private MModelServer As IModelServer
Private MRibbonModel As RibbonModel
Public Function ModelServer() As IModelServer
If MModelServer Is Nothing Then
Set MModelServer = Application.COMAddIns(COMAddInName).Object _
.NewBetterRibbon(New ResourceLoader)
End If
Set ModelServer = MModelServer
End Function
Private Sub Workbook_Activate()
If MRibbonModel Is Nothing Then
Application.COMAddIns(COMAddInName).Object.RegisterWorkbook ThisWorkbook.Name
Set MRibbonModel = New RibbonModel
End If
End Sub
' Depending on Workbook location, pops-up a dialog to assist with setting debug breakpoints.
Private Sub Workbook_Open()
If DeskTop(False) = "D:\Users\Pieter\Desktop\" _
Or ThisWorkbook.Path = DeskTop(True) & "Example Workbooks" _
Or ThisWorkbook.Path = DeskTop(False) & "Example Workbooks" Then _
MsgBox "Pause for Ctrl-Break to ease debugging." & vbNewLine & vbNewLine & _
"This message can be disabled by moving the workbook" & vbNewLine & _
"out of the Desktop folder 'Example Workbooks'.", _
vbOKOnly, ThisWorkbook.Name
End Sub
The Workbook_Activate event is programmed as the controls for this workbook are automatically deactivated when the workbook loses focus. So the preprogrammed customizable controls from the Add-In are all available to every workbook.
Also included:
- VBA Source Exporter that unloads all VBA code for an MS-Excel or MS-Access project to a directory sibling to the workbook/database; named either eponymously with the suffix VBA or .\src. A great time saver for managing VBA code in source control. There is a Ribbon Group in the Add-In with three controls accessing this functionality:
- A Toggle between use of the directory '\src and a directory eponymouswith the workbook/database name.
- A Selected Projects (Action)Button for exporting VBA code from a directory of projects.
- A Current Workbook (Action)Button that immediately exports the VBA code from the current workbook.
- External Links Analyzer that collects details on all External Links in a list of Workbooks, and reports them to three worksheets in the current worlkbook: Links Analysis, Externally Linked Files List, and Parsing Failures.