Personal ribbons are a very convenient and fancy way to run VBA code and to provide an easy user interface if the workbook has to be used by many people.
Starting with Excel 2007 the process of creating a ribbon has become simpler and more flexible.
In order to create the ribbon you need a little piece of software called Custom UI Editor for Microsoft Office. It is a free tool and you can download it from
The UI editor is nothing else than an XML editor. Launch the editor and open the file for which you want to create a custom Ribbon.
I've created a new xlsm file named Custom Ribbon and your editor will look like this:
Right click on the Excel icon of the file you've just selected and choose "Office 2007 Custom UI Part". This will create a new project called custom UI.xml
At this point you need to add the following XML markup to the file:
As you can see there is a hierarchy in the structure. The Ribbon contains one or more tabs, each tab contains one or more groups and each group contains controls as Buttons, Dropdowns, Split Buttons etc...
In this example I've used buttons, play around with the other controls as you wish.
*TIP Never, ever work with both your editor and your Excel file open at the same time. If the Excel file is open and you save your xml file you will get no warning whatsoever but all the changes you have made will not be saved
Now that we have finished writing the code for the first Ribbon menu item we need to write the code which will be launched . To do that we close the UI editor and we open the Excel file.
Open the VBA editor and add a module. In the module paste the following code:
Option ExplicitPublic Sub NavigateSheets(control As IRibbonControl) Dim lTag As Long lTag = CLng(control.Tag) Application.ScreenUpdating = False Select Case lTag Case 0: wksSheet1.Activate MsgBox ("This is Sheet1") Case 1: wksSheet2.Activate MsgBox ("This is Sheet2") Case 2: wksSheet3.Activate MsgBox ("This is Sheet3")End SelectEnd SubPublic Sub WhoAmI(control As IRibbonControl)MsgBox ("Hello my name is Yuppidu")End Sub
As you can notice I have renamed the sheets in the workbook. I advise you to do the same when you write user interfaces which are going to be used by many people. If you use the name which appears in the tabs, if anybody changes that name the code will brake. On the other hand renaming the sheets in the property window of VBA will make the code stronger and it will not brake if sheet tabs name are changed.
To add another menu item just add a group and follow the same procedure.
Comments (0)