How to add button in excel ribbon for call custom add-in?

Question

I have prepared complex add-in with shortcuts used in process. Users would like to call it with the easiest way. It has to be added as addin, modifications in personal workbook are not allowed.

Is it possible to add custom button for calling userform outside of Add-ins section in ribbon with VBA? I have tried several ways to add button in Add-in section, but I have it done in 100% correctness. Button adds but there is a problem with duplicating it, it does not remove with excel close.

https://github.com/fernandreu/office-ribbonx-editor – RetiredGeek Aug 16 '20 at 14:25 — RetiredGeek, Aug 16 '20 at 14:25

score 0 · Answer 1 · answered Aug 16 '20 at 23:47

You can use CommandBars and CommandBarControls classes to accomplish your work.

Please note some considerations:

When adding a control, you should specify the Temporary property as True or False
If you don't remove your controls before adding more, and they're the same, they will be duplicated. Best way to remove appropriately is when adding buttons, add some unique text to the Tag property (so you don't remove other controls that may be there).

Adding as Temporary (all is happening in the AddIns tab):

Dim cBar as CommanBars
Dim cBtn as CommandBarButton

Set cBar = CommandBars("Worksheet Menu Bar")
Set cBtn = cBar.Controls.Add(Type:=msoControlButton, Temporary:=True)

With cBtn
    .Visible = True
    .Enabled = True
    .Tag = "coolButton"
    .FaceId = 123
    .Caption = "Cool Trick"
    .Style = msoButtonIconAndCaption
    .TooptipText = "this button does something magically!!"
    .OnAction = "Module1.SomeMacro"
End with

Removing:

Dim cBar as CommanBars
Dim cBtn as CommandBarButton

Set cBar = CommandBars("Worksheet Menu Bar")

For Each cBtn in cBar.Controls
    If cBtn.Tag = "coolButton" Then: cBtn.Delete
Next cBtn

How to add button in excel ribbon for call custom add-in?

1 Answers1