Building an Add-in for Excel, the User interface
Add-ins need a way for the user to interact with them. They may need custom forms (Userforms), perhaps create worksheets for data entry and reporting, right-click menu's and other user interface additions. This add-in will work using some items on a tab on the ribbon.
Our add-in will only have a very limited userinterface, everything to use the add-in will be located on the Ribbon. Creating a custom ribbon userinterface is not for the faint at heart. Luckily we have various excellent resources:
Using the Custom UI Editor, I opened the workbook I am working on (I previously saved and closed the workbook from Excel!).
I inserted an Office 2007 CustomUI part into the workbook:
Using this part ensures the add-in shows up in both the Office 2007, 2010, 2013 and 2016 ribbon. Then I wrote this xml code in the window (well, I copied more than half of it from some example and modified it to fit my needs, like any programmer would do):
<tab id="rxJKPSheetTools" label="SheetTools">
<group id="rxJKPSheetToolsGeneral" label="Sheet Tools">
label="Table Of Contents"
The Custom UI editor looks like this after entering this code:
A number of the items you see in this xml are so-called callbacks. That means Excel is expecting VBA code for them in the workbook in question. You can click the right-most button on the toolbar of the Custom UI editor screen to have it generate the empty Sub ... End Sub stubs for you.
The xml contains these callbacks:
- onLoad (called when the workbook loads)
- getItemCount (returns the number of items for a dropdown control)
- getItemLabel (fetches the nth item for the dropdown)
- onAction (called when a button is clicked or a dropdown selection is made)
- getSelectedItemIndex (called to fetch the item in the dropdown that needs to be selected)
You may have noticed the convoluted routine names (like "rxJKPSheetToolsbtnSheets_Click"). Especially the "rxJKPSheetTools" part. Callback names must be unique in an Excel instance. And I really mean Unique. No other workbook open in Excel can have a sub name that coincides with any of your call backs. Otherwise you risk that the routine in the other workbook is called by the ribbon! This is why I prefix the routine names with something that uniquely identifies the workbook that contains the RibbonX code.
After copying the code stubs from the UI editor, I inserted a new module called modRibbonX and pasted the stubs into it:
Sub rxJKPSheetToolscustomUI_onLoad(ribbon As IRibbonUI)
Sub rxJKPSheetToolsbtnInsertTOC(control As IRibbonControl)
'Callback for rxJKPSheetToolsbtnSheets getItemCount
Sub rxJKPSheetToolsbtnSheets_Count(control As IRibbonControl, ByRef returnedVal)
Public Sub rxJKPSheetToolsbtnSheets_getItemLabel(control As IRibbonControl, Index As Integer, ByRef returnedVal)
'Callback for rxJKPSheetToolsbtnSheets getSelectedItemIndex
Sub rxJKPSheetToolsbtnSheets_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
Sub rxJKPSheetToolsbtnSheets_Click(control As IRibbonControl, id As String, Index As Integer)
Next: adding code to the ribbon callbacks