<

Build a personal Ribbon in few easy steps

Published on
8,800 Points
2,100 Views
2 Endorsements
Last Modified:
Approved
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

http://softadvice.informer.com/Microsoft_Office_Ui_Editor.html 

or many other sites, just do a search on Google.

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:

 UI Interface step one
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:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui>

this is the very first line in the project window.

Let's build a ribbon with multiple groups and an icon for each group. First of all let's add the icons we will be using for our Ribbon.

On the editor click on the picture icon and select the first icon by browsing to it. I normally use .png files and keep the size at 60px.

Once you have added the icons they will be visible under CustomUI.xml in the left panel of the editor.

Now we have to build the Ribbon structure. This is done by adding the appropriate tags to the xml file.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
	<ribbon>
		<tabs>
			<tab id="gsExpertExchange" label="Experts Exchange">
				<group id="gsNavigateGroup" label="Navigation">
					<menu id="gsNavigate" label="Navigate" size="large" image="navigate">
						<button id="gsSheet1" label="Go to Sheet1" onAction="NavigateSheets" tag="0"/>
						<button id="gsSheet2" label="Go to Sheet2" onAction="NavigateSheets" tag="1"/>
						<button id="gsSheet3" label="Go to Sheet3" onAction="NavigateSheets" tag="2"/>
          				<menuSeparator id="gsNavSep1"/>
						<button id="gsWhoAmI" label="Say who?" onAction="WhoAmI"/>
					</menu>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Open in new window


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.

Our new ribbon
Open the VBA editor and add a module. In the module paste the following code:

Option Explicit


Public 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 Select


End Sub

Public Sub WhoAmI(control As IRibbonControl)

MsgBox ("Hello my name is Yuppidu")

End Sub

Open in new window


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.



Enjoy!
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
	<ribbon>
		<tabs>
			<tab id="gsExpertExchange" label="Experts Exchange">
				<group id="gsNavigateGroup" label="Navigation">
					<menu id="gsNavigate" label="Navigate" size="large" image="navigate">
						<button id="gsSheet1" label="Go to Sheet1" onAction="NavigateSheets" tag="0"/>
						<button id="gsSheet2" label="Go to Sheet2" onAction="NavigateSheets" tag="1"/>
						<button id="gsSheet3" label="Go to Sheet3" onAction="NavigateSheets" tag="2"/>
          				<menuSeparator id="gsNavSep1"/>
						<button id="gsWhoAmI" label="Say who?" onAction="WhoAmI"/>
					</menu>
				</group>
				<group id="gsHelpGroup" label="Help">
					<menu id="gsHelp" label="Help" size="large" image="box">
						<button id="gsHelpp" label="Help me" onAction="HelpInfo"/>
					</menu>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Open in new window

2
Comment
Author:yuppydu
0 Comments

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Join & Write a Comment

This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month