Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Build Excel Add-in > User interface
Deze pagina in het Nederlands

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.

Ribbon modifications

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:

Inserting a customUI part

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):

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxJKPSheetToolscustomUI_onLoad">
    <ribbon>
        <tabs>
            <tab id="rxJKPSheetTools" label="SheetTools">
                <group id="rxJKPSheetToolsGeneral" label="Sheet Tools">
                    <dropDown id="rxJKPSheetToolsbtnSheets"
                        getItemCount="rxJKPSheetToolsbtnSheets_Count"
                        getItemLabel="rxJKPSheetToolsbtnSheets_getItemLabel"
                        onAction="rxJKPSheetToolsbtnSheets_Click"
                        sizeString="MMMMMMMMMMMMMMMMMMMMMMMM"
                        label="Sheets"
                        getSelectedItemIndex="rxJKPSheetToolsbtnSheets_getSelectedItemIndex"
                    />
                <button id="rxJKPSheetToolsbtnInsertToc"
                    label="Table Of Contents"
                    size="large"
                    onAction="rxJKPSheetToolsbtnInsertToc"
                    imageMso="CustomTableOfContentsGallery" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

The Custom UI editor looks like this after entering this code:

Custom UI Editor

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:

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:

Option Explicit

Sub rxJKPSheetToolscustomUI_onLoad(ribbon As IRibbonUI)
End Sub

Sub rxJKPSheetToolsbtnInsertTOC(control As IRibbonControl)
End Sub

'Callback for rxJKPSheetToolsbtnSheets getItemCount
Sub rxJKPSheetToolsbtnSheets_Count(control As IRibbonControl, ByRef returnedVal)
End Sub

Public Sub rxJKPSheetToolsbtnSheets_getItemLabel(control As IRibbonControl, Index As Integer, ByRef returnedVal)
End Sub

'Callback for rxJKPSheetToolsbtnSheets getSelectedItemIndex
Sub rxJKPSheetToolsbtnSheets_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
End Sub

Sub rxJKPSheetToolsbtnSheets_Click(control As IRibbonControl, id As String, Index As Integer)
End Sub

Next: adding code to the ribbon callbacks


 


Comments

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].