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
- Basic Knowledge on Extensible Markup Language(XML)
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
- If you have not done so, install Office Custom UI Editor the link provided above.
- Create an excel file and name it as “My First Custom Office Ribbon.xlsm”
- Open the Office Custom UI Editor application
- 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
then goto to Insert menu and select “Office 2010 Custom UI Part“, and a childnode “CustomUI14.xml” has been added.
The Custom Ribbon XML
<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 idMso="GroupClipboard" />
<group idMso="GroupFont" />
<group idMso="GroupEnterDataAlignment" />
<group idMso="GroupEnterDataNumber" />
<group idMso="GroupQuickFormatting" />
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
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"
MsgBox "Error: " & Err.Description, vbCritical, "Error"
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
My First Custom Office Ribbon
How to customize and add ribbon in Office 2010,