Excel Ribbon Development Guide
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
- Excel to word - Edit word documents from Excel
- Excel data analysis using VBA loops
- Send email from Excel
- Get stunning excel dashboards.
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.
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.
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.
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.
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)
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