Creating Ribbon Add-In

[masouder]

I am looking for an online source that explains how to build an .xlam add-in that creates a custom ribbon with various commands using VBA. The add-in has to work with 2016 and 365. I thought this would be relatively easy to find, but most of the sites I found on Google include download tools that purport to do most of the work. I would prefer just to learn how to do it on my own. I assume that it is not too difficult to do, but perhaps that is incorrect given the many tools that I found.

Any suggestions are appreciated.

 

[RoryA]

You are indeed incorrect, unless all you want to do is add some controls to the built-in Addins tab, which can be done using the old Commandbars code.

 

[masouder]

Thanks, Rory. Adding commands to the Addins tab is perfectly fine. Any additional suggestions on where to get more details on how to do this? Or do I just need to search for Commandbars?

 

[RoryA]

Searching for commandbars should find you numerous examples.

 

[masouder]

Searching for commandbars should find you numerous examples.

Rory, I'm hoping you might be able to offer a bit more help. I found some sample code which was pretty straightforward. I created a procedure in a file (AddInBuild.xlsm) that added a single item (a button caption) to the Add-In ribbon. Code:

Sub AddNewCB()
Dim myCommandBar As CommandBar, myCommandBarCtl As CommandBarControl
On Error GoTo AddNewCB_Err
Set myCommandBar = CommandBars.Add(Name:="Sample Toolbar", Position:=msoBarFloating)
myCommandBar.Visible = True
Set myCommandBarCtl = myCommandBar.Controls.Add(Type:=msoControlButton)
With myCommandBarCtl
.Caption = "Center Across"
.Style = msoButtonCaption
.TooltipText = "Display Message Box"
.OnAction = "TestSub()"
   End With
AddNewCB_Err:
Debug.Print Err.Number & vbCr & Err.Description
Exit Sub
End Sub

That item calls a procedure in an Add-In that I created and added to Excel. The add-in procedure centers across selection then displays a message stating that the code is done. Code:

Public Sub TestSub()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
    End With
    MsgBox "Done"

End Sub

I ran the procedure to add the item to the Add-Ins ribbon, and that worked fine. I added the Add-In and it is present. The problem is that when I click the new item on the ribbon, the code does not center across selection, but displays the "Done" message so I know the procedure was called. Before clicking the item, I entered text in a cell then selected that cell and two cells to the right.

I have attached the two files that I used.

Any ideas why the center across selection code fails to run?

 

[RoryA]

I'd suspect that the two cells to the right weren't actually empty. If you saw the message then the code must have run. However, the OnAction command should not have brackets on the end, it should just be:

.OnAction = "TestSub"

 

[masouder]

Rory, amazingly, removing the brackets solved the problem. (I had previously confirmed that the cells to the right were empty.) I'm surprised because with the brackets the code does run - as evidenced by the message at the end - but the code to center across selection did not work. Very odd!