Add macros to quick access toolbar (QAT)

<?xml encoding=”UTF-8″>By Adam Nagy

If you want to add macros to the quick access toolbar then the following code should be useful.

It would also be possible to iterate through all the VBA macros and add them like that, but in this case we assume we already know the name of the macros we want to add.

Sub AddMacros()
' List of Macros we want to add
Dim macros(1) As String
macros(0) = "Module2.qwe"
macros(1) = "Module1.test"
Dim cds As ControlDefinitions
Set cds = ThisApplication.CommandManager.ControlDefinitions
Dim ribbons As ribbons
Set ribbons = ThisApplication.UserInterfaceManager.ribbons
Dim macro As Variant
For Each macro In macros
' Check if there is already a ControlDefinition for it
Dim cd As ControlDefinition
For Each cd In cds
If TypeOf cd Is MacroControlDefinition And _<br>        InStr(1, cd.InternalName, macro) > 0 Then 

        Exit For
End If
Next
' Add it if not
If cd Is Nothing Then
Set cd = cds.AddMacroControlDefinition(macro)
End If
Dim ribbon As ribbon
For Each ribbon In ribbons
' Check if QAT already contains it
Dim cc As CommandControl
For Each cc In ribbon.QuickAccessControls
If cc.ControlDefinition Is cd Then
Exit For
End If
Next
' Add it if not
If cc Is Nothing Then
Set cc = ribbon.QuickAccessControls.AddMacro(cd)
End If
Next
Next
End Sub