Customizing Excel Ribbon

Quick Start to adding a ribbon group and buttons, and underlying code to become an excel 2010 add-in.

Create an empty folder. EXCELaddins to hold your working files. Within this folder create 2 subfolders named customUI and _rels.

Open a new blank workbook. Open the Visual Basic Editor (VBE), try Alt + F11. Create your VB subs or functions that will be called by your ribbon buttons. In your working folder, save this workbook as an excel add-in file (.xlam) with a descriptive name that will appear in Excel’s Add Ins dialogue.

Edit 2 files within your .xlam file package. Open your .xlam file with your favorite zip program. May require renaming your .xlam to .zip.

Withing the .xlam archive, extract the _rels\rels file to EXCELaddins\_rels\rels. Open the rels file In a plain text editor, and add the following relationship to the <Relationships> section:

<Relationship Id="myUniqueID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml" />

Save the changes to your _rels\rels file.

Using a plain text editor create a \customUI\customUI.xml file that contains ribbon groups and buttons to call the VB code. Use the below examples:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="myuniqueid1" label="My Ribbon Tab Name">
        <group id="myuniqueid2" label="My Tab Group Name" >
          <button id="myuniqueid3" label="My Group Top Button Name" size="normal" onAction="myEventSub1" />
          <button id="myuniqueid4" label="Second Button Name" size="normal" onAction="myFunction2" />
          <button id="myuniqueid5" label="Third Button Name" size="normal" onAction="mySub3" />
        </group >
        <!-- group id="myuniqueid6" label="This Group Will Not Appear in the Ribbon" >
          <button id="myuniqueid7" label="Other Group Top Button Name" size="normal" onAction="MyOtherEventHandlerSub" />
        </group -->
      </tab>
    </tabs>
  </ribbon>
</customUI>

Add your updated rels file and your customUI folder (contains your customUI.xml file) back into your zipped archive (your .xlam file).

Copy your working .xlam to the excel addins folder, default windows location was C:\Users\User\AppData\Roaming\Microsoft\AddIns.

Open Excel, navigate to the “Developer” tab on the ribbon, and open “Add Ins”. Now in Excel’s Add Ins dialogue, check your .xlam file to enable it. Restart Excel to try out your Excel add in.

To download a functional example of the above, see our “Fixing Excel Comments” post.