Excel Ribbon Development Guide

<Back to home

Introduction

This page contains instructions and advice to help you create a custom ribbon tab in Excel using Easy Ribbon Builder.

To be 100% clear, Easy Ribbon Builder allows you to build your custom Excel ribbon tab.  However, you must develop your own code in VBA for the action that happens when the button is clicked. VBA is included free with Excel.  This page gives you some tips for developing VBA code.  If you want me to write your VBA code for you, then just email a description of the work and I’ll provide a free quote.

Quick Resources

Introduction to VBA

Excel button design guide

Downloads

  • Download the free version of Easy Ribbon Builder.
  • Download a simple example of a published Easy Ribbon Builder Spreadsheet.

Useful VBA code

Do I need a custom ribbon?

This blog will help answer this question, it provides a complete design guide for buttons to trigger macros in Excel.

How to create a custom ribbon?

Follow these steps to create your own ribbon:
Step 1. Open Easy Ribbon Builder.  If you don’t have this you can either download the free version or the professional version.

Step 2. (not required for free version) Fill out the sheet below to define the structure of your ribbon tab, then click the done button.  You can have up to 10 groups of buttons.  Button types available are split, drop-down and simple buttons.  90% of projects only require these buttons, however if you need more complex types then after you complete step 3 you can edit the workbook's XML file using RibbonX.

Create custom ribbon tab

Step 3.  Fill out the sheet below for each group of buttons, then click the done button.  
• Immediately see changes to your ribbon as you fill out this workbook.
• For tips on filling out each column, hover your mouse over the column heading.
There is also an inbuilt icon tool you can use to find icons within a database of more than 7000 icons.

Custom ribbon tab

Step 4.  Add code for each of your buttons actions.  

Clicking a button (or menu item) triggers the Button_Click procedure.  Add code to this procedure to set actions. To access the Button_Click procedure: 
Press Alt + F11 > click 'My_Code' module > scroll down to Button_Click. Or just click on any of your ribbon buttons to take you to the Button_Click procedure directly.

Example of Action code

Public Sub Button_Click(ButtonItem As Variant)
    Dim jokeInput As String
    If ButtonItem.ControlID = R_Joke_G1B2.ControlID Then
        jokeInput = InputBox("What should you do if an Aussie throws a grenade at you?", Title:="Classic Joke")
        If InStr(jokeInput, "pull the pin and throw it back") > 0 Then
            MsgBox "You got it correct!", Title:="Classic Joke"
        Else
            MsgBox "Incorrect" & vbNewLine & vbNewLine & "Answer: The women spit further.", Title:="Classic Joke"
       End If
   End If
End Sub

Note 1 - Button identification methods

Method 1:  If ButtonItem.Label = "Joke" then

This method is quick but not robust.  If the Label changes name or if the button has the same label as another button then you can get errors. 

Method 2:  If ButtonItem.ControlID = R_Joke_G1B2.ControlID Then

This method is robust as the Control ID is unique and fixed

Note 2 - Finding a button object name in VBA

This is easy.  Anywhere in VBA just type "CTRL + Space" then start typing in your button label to find it. 

Excel ribbon button ID naming convention

If you type in enough text that it is unique to a button object name then its full object name will be inserted.  So in this example I typed "R_J" then "CTRL + space" then the full name "R_Joke_G1B2" was inserted.

Step 5.  (Optional) Add code to set rules for dynamic changes to your ribbon. 

This example code below greys out buttons depending on what sheet is selected.

Custom Excel ribbon tab CustomUI code

 

 

Information about VBA Ribbon Objects

Objects created automatically

As you develop your ribbon with Easy Ribbon Builder you'll notice that there is a column titled 'VBA object name'.  These names are computer generated and when you publish it, Easy Ribbon Builder will define all ribbon items as objects using these names.  These objects make it very easy to read or write ribbon item properties with VBA code.  The components of these names are:

  • "R_" at the start allows the user to easily filter for only the ribbon items.
  • The item label is then included so the user recognises the item.
  • Tab for only the ribbon tab item
  • GX where X is the group number 1-10 (not for ribbon tab item)
  • BX where X is the button number 1-10 (not for ribbon tab or group items)
  • MX where X is the menu number 1-10 (only for drop-down or split buttons)

Excel custom ribbon naming convention 

Read / Write properties

All ribbon item objects that Easy Ribbon Builder has defined in VBA can be read or written.  The only exception is ControlID which can only be read.

Just to be 100% clear, the argument ButtonItem in the Button_Click procedure refers to a ribbon item but is not a ribbon item itself. ButtonItem can only be read but not written.

Read and Write - example A

If R_Joke_G1B2.ControlID.enabled = False Then 
      R_Joke_G1B2.ControlID.enabled = True
End if

Read and Write - example B

If R_Tab.Label = "My Ribbon Tab" Then  
     R_Tab.Label = "New Ribbon Tab"
End if

Read - example C

If ButtonItem.ControlID = R_Joke_G1B2.ControlID then
     Msgbox "you clicked the Joke button"
End if

Write Error - ControlID - example D

If R_Joke_G1B2.ControlID = "R_1234" 

Error - ControlID can only be read

Write Error - ButtonItem - example E

ButtonItem.Label = "New Button"

Error - ButtonItem can only be read.  This is used as a argument  in the Button_Click procedure, it refers to a button item but is not the button item itself

Object definitions

This is a list of ribbon item objects in RibbonObjectsDefined module.  Easy Ribbon Builder has automatically defined these objects for you.  These objects make it very easy to read or write ribbon item properties with VBA code.

 For a full list of these objects, simply type "R_" anywhere in VBA code > Press CTRL + Space

1. CustomButton - Object information

   Properties:  (Where R_Label_GXBX represents a button name)
       R_Label_GXBX.Label               (Button label; String; read; write)
       R_Label_GXBX.ControlID           (ControlID of button which is different to the button name; R_GXBX; readonly)
       R_Label_GXBX.Visible             (Property; Boolean (True or False); read; write)
       R_Label_GXBX.GreyedOut           (Property; Boolean (True or False); read; write)
       R_Label_GXBX.ButtonSizeLarge     (Property - Where false sets the size to small; Boolean (True Large or False); read; write)
       R_Label_GXBX.Image               (Property - must match a microsoft MSO image ; String ; read; write)
       R_Label_GXBX.HoverCommentTitle   (Buttons hover comment title; String ; read; write)
       R_Label_GXBX.HoverCommentBody    (Buttons hover comment body text; String ; read; write)

2. CustomMenu - Object information

   Properties:  (Where R_Label_GXBXMX represents a menu items name)
       R_Label_GXBXMX.Label               (Menu item label; String; read; write)
       R_Label_GXBXMX.ControlID           (ControlID of menu item which is different to the objects name; R_GXBXMX; readonly)
       R_Label_GXBXMX.Visible             (Property; Boolean (True or False); read; write)
       R_Label_GXBXMX.GreyedOut           (Property; Boolean (True or False); read; write)
       R_Label_GXBXMX.Image               (Property - must match a microsoft MSO image ; String ; read; write)
       R_Label_GXBXMX.HoverCommentTitle   (Menu items hover comment title; String ; read; write)
       R_Label_GXBXMX.HoverCommentBody    (Menu items hover comment body text; String ; read; write)

3. CustomGroup - Object information

   Properties:  (Where R_GX represents a groups name)
       R_GX.Label               (group label; String; read; write)
       R_GX.ControlID           (ControlID of group which is different to the objects name; R_GX; readonly)
       R_GX.Visible             (Property; Boolean (True or False); read; write)

4. CustomRibbonTab - Object information

  Properties:  (Where R_Tab is this objects actual name.  There is only one of these objects)
       R_Tab.Label               (Ribbon Name; String; read; write)
       R_Tab.ControlID           (ControlID of group which is different to the objects name; R_Tab; readonly)

5. Ribbon Items - Collection information

  This collection contains ALL Ribbon objects of 1-4 above.  This collection is useful to:
    - Find a particular ribbon object
    - Manipulate a large number of ribbon objects
    - Determine the number of ribbon items
 

Example code using RibbonItems:

Sub GreyOutMenuItems ()

     Dim AnyItem As Variant
     Dim x as integer
     x = 0
     For Each AnyItem In RibbonItems
        If TypeOf AnyItem Is CustomMenu And AnyItem.GreyedOut = False Then
            AnyItem.GreyedOut = greyOut
            x = x + 1
        End if
     Next AnyItem
     Msgbox x & " Menu items were greyed out.
 End sub

6. GroupItems(X) - Collection information

Where X is the group number.  Setting X to 0 will produce an error.  Note that the free version only has one group so this is only useful for the professional version.

Example code using GroupItems collection:
  Sub GreyOutGroup(groupNumber As Integer)
    Dim AnyItem As Variant
    For Each AnyItem In GroupItems(groupNumber)
        If (TypeOf AnyItem Is CustomMenu or TypeOf AnyItem Is CustomButton) And AnyItem.GreyedOut = False Then
            AnyItem.GreyedOut = greyOut
        End if
    Next AnyItem
End Sub

 

 <Back to home