Excel: Creating Your Own Tabs With XML And VBA

In order to create you own nice looking tabs you need the Custom UI Editor for Microsoft Office. You can download it for free.

How is this different from choosing File, Options, Customize Ribbon and adding a new tab with new groups and then commands to the group? You can also move the new tab to any position. Then your new tab is always there. Even when you don't open the specific file with the right VBA.

In my example the outcome looks like this, a nice tab, placed in front of the Home Tab. You will only get this tab when you start the specific example (which you can download).

The XML behind this:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <ribbon startFromScratch="false">
    <tabs>
      <tab id="MaxIlze" label="Dossier Overzicht" insertBeforeMso="TabHome">
        <group id="customGroup1" label="Adressen verrijken">
          <button id="customButton1" label="Adressen toevoegen" size="large" 
onAction="toevoegenpchnr" imageMso="QueryAppend" />
         <button id="customButton2" label="Gegevens toevoegen" size="large" 
onAction="vullen" imageMso="DatabaseAccessBackEnd"/>
         <button id="customButton3" label="Adres zoeken" size="large" 
onAction="zoekenpostcode" imageMso="FindDialog"/>
         <button id="customButton4" label="Dossier zoeken" size="large" 
onAction="zoekendossier" imageMso="FindDialog"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Comments

• insertBeforeMso="TabHome" will place the tab before the Home Tab
• onAction="toevoegenpchnr" refers to the VBA behind the file
• imageMso="QueryAppend" refers to the Microsoft buttons; you could use image to refer to you own buttons
• label="Adressen toevoegen" refers to the label underneath the buttons

The VBA behind the file:

Sub toevoegenpchnr(control As IRibbonControl)

    MsgBox "toevoegen"

End Sub

Sub vullen(control As IRibbonControl)

    MsgBox "vullen"

End Sub

Sub zoekenpostcode(control As IRibbonControl)

    MsgBox "zoeken postcode"

End Sub

Sub zoekendossier(control As IRibbonControl)

    MsgBox "zoeken dossier"

End Sub

Comment

• control As IRibbonControl, this needs to be added to the VBA procedure

You can download ExcelTab.xlsm via:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing