Our Featured Articles:
1 2 3 4 5 6 7 8 9 10


How to customize and add ribbon in Office 2010

August 17, 2013 admin Microsoft Office

Post to Twitter





How to Customize and add ribbon in Microsoft Office 2010

  • Requirements: Microsoft Excel 2010, Office Custom UI Editor, Knowledge in XML, Microsoft Visual Basic(VBA)
  • Programming Level: Basic
  • Language: Microsoft Visual Basic for Applications (VBA)
  • Office Custom UI Editor
  • Office2010IconsGallery
  • Basic Knowledge on Extensible Markup Language(XML)

Scenario

I have been working on how to create my own Ribbon control and add custom button on it to execute my VBA macro code created using the Visual Basic for Applications, If you do realized that how Office be more productive in terms of accessibility and features that can make your Excel worksheet more useful, I have created steps on how to do it so that it will be easily for you to create one for you. You gonna need your basic knowledge on XML in which I am going to create separate article for that, but as of now you need to research on how to understand XML because you gonna need that in this exercises.

Procedures on how to start with creating your own ribbon control and custom button

  1. If you have not done so, install Office Custom UI Editor the link provided above.
  2. Create an excel file and name it as “My First Custom Office Ribbon.xlsm”
  3. Open the Office Custom UI Editor application
  4. Read the Instruction provided below.

Instructions on how to do the procedure above

Please install the Office Custom UI Editor the link is provided above and download the file, install it. Create an empty Microsoft Excel Worksheet and name it as given above anyway you could name it any name you like but it is a best practice just to follow my instructions so that all will be done successfully. You have noticed that the extension is “xlsm” this a Macro-Enabled Excel Worksheet, Why? Because we shall have some basic Visual Basic Code to test the ribbon button associated on it, just select “Excel Macro-Enabled Workbook(*.xlsm)” on Save As Type during saving the Excel file, save it anywhere you like because we gonna need that later on, just make sure you know where it was saved.

Working with Office Custom UI Editor

Close the Excel File if it is open, Open the Office Custom UI Editor, on File click open and locate the Excel file we have created earlier select it as shown below:

Custom UI Editor

Custom UI Editor

then goto to Insert menu and select “Office 2010 Custom UI Part“, and a childnode “CustomUI14.xml” has been added.

The Custom Ribbon XML

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon>
		<tabs>
			<tab id="customTab" label="SourceHints" insertAfterMso="TabHome">
				<group id="SampleMenu" label="Sample SourceHints group tab">
					<button id="btnRunMacro" label="Run sample Macro" imageMso="ChartResetToMatchStyle" size="large" onAction="RunMacro" supertip="This Button rans macro created using VBA." />
					</group>
				<group idMso="GroupClipboard" />
				<group idMso="GroupFont" />
				<group idMso="GroupEnterDataAlignment" />
				<group idMso="GroupEnterDataNumber" />
				<group idMso="GroupQuickFormatting" />
			</tab>
		</tabs>
	</ribbon>
</customUI>

Now copy and paste the xml above on the right pane of Custom UI Editor exactly as it was created in here, you have noticed “CustomTab” id labeled as “SourceHints” is a ribbon tab that will be created on an Excel menu, the “SampleMenu” group id is a custom group I created, the four groups: GroupClipboard, GroupFont, GroupEnterDataAlignment, GroupEnterDataNumber, GroupQuickFormatting are system groups embedded on excel application in which we can add system group on our custom menu. The finish Custom Ribbon is shown below:

Finish Output Design for Custom UI on Office 2010

Finish Output Design for Custom UI on Office 2010

Visual Basic for Application Simple Macro Code

Now we have done with UI design let us now create a simple macro code just to demonstrate how macro is being called from our custom button ribbon control that we have created. You notice on our xml above the onAction=”RunMacro” under the button id “btnRunMacro” the “RunMacro” is the associated VBA subroutine that we are about to create now so that when user click the “Run Sample Macro” on ribbon menu button this subroutine will be executed.

If you have not done so

In order for you to be able to create a visual basic code on excel the “Developer” tab should be visible, if you cannot see the “Developer” tab you allow let excel know that you want to use VBA Macro, because by default the Developer tab is not visible this is for advance Excel users and not for typical and normal users. To show the Developer tab, on File Menu goto Options, on “Excel Options” select the Customized Ribbon, on the “Customize the Ribbon” Combobox, select “Main Tabs” and tick the Developer checkbox and click ok, now on Excel, select the Developer tab, under the “Code” group select Visual Basic, now you will be redirected to Visual Basic Code editor, here you can do anything that you can do on Microsoft Visual Basic 6.0 IDE, the MS Excel 2010 uses Microsoft Visual Basic 6.0 Programming. On VBAProject Project Explorer, right click and Insert->Module, and on the Module1 Code editor, copy and Paste the code below exactly as it is:

Public Sub RunMacro(control As IRibbonControl)
Dim msg As String
On Error GoTo RunErr

msg = "Today is: " & Format(Now, "dddd") & ", " & Format(Now, "mmmm dd, yyyy") & vbCrLf
msg = msg & "VBA is a powerful tool to enhance " & vbclrf
msg = msg & "Office capabilities beyond your needs."
MsgBox msg, vbInformation Or vbOKOnly, "VBA is Fun"

RunExit:
    Exit Sub
RunErr:
    MsgBox "Error: " & Err.Description, vbCritical, "Error"
    Resume RunExit
End Sub

you noticed on RunMacro Subroutine we have a parameter “control As IRibbonControl” this control is a type IRibbonControl in which represents the button that we have created in Custom UI Editor via XML, as you click the “Run Sample Macro” button the subroutine “RunMacro” will be executed and evaluated, you can do anything you want here as you do in Microsoft Visual Basic 6.0 IDE.

Last thing you need to do

Save the changes you been made on VBA editor, and on File menu of VBA Code editor select “Close and return to Microsoft Excel” and go on click the button and see what have you done. To download the sample Excel Macro click the link below:

Sample Microsoft Excel Macro-Enabled File

Kindly like our Fan Page at: Like Us at SourceHints

My First Custom Office Ribbon

VN:F [1.9.6_1107]
Rating: 10.0/10 (2 votes cast)
VN:F [1.9.6_1107]
Rating: 0 (from 0 votes)

How to customize and add ribbon in Office 2010, 10.0 out of 10 based on 2 ratings


Post to Twitter

Keywords: , , , , , , , , , ,

Other reading this article are also reading these:

Custom UI Editor, excel, Macro, MSOffice, ribbon,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

87,858 Spam Comments Blocked so far by Spam Free Wordpress

HTML tags are not allowed.

Powered by WordPress 3.5.1. Designed by elogi. Allright Reserved SourceHints 2010-2014