How to Use Excel VBA to Customize Ribbon (with Easy Steps)

Sometimes a user might need a custom ribbon if he wants to have quick access to the macros and commands he uses frequently. Excel allows users to customize the ribbon according to their needs easily. In this article, we will walk you through five easy steps in Excel VBA to customize ribbon without using any third-party software.

How to Launch VBA Editor in Excel

To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.

Then go to the Insert tab and click on Module to open the code Module.

Excel VBA to Customize Ribbon: 5 Easy Steps

In this article, we will demonstrate five quick and easy steps in Excel VBA to customize ribbon without installing any additional software. We will use the Windows Notebook for this purpose.

Step 1: Save Excel File as Zip File

In the first step, we will change the extension of the Excel file. We have a sample Excel file that has xlsm extension. We want to change it to zip.

First, click on the View panel and tick the checkbox of File name extensions.
As a result, the file extension will be visible in the file name.
Rename the file by changing the extension to zip.

Double-click the zip file to see the folders inside it. The zip file should contain _rels, docProps, and xl folders.

Step 2: Create an XML File to Add New Groups, Tabs, and Buttons

In this step, we will create an XML file to add groups, tabs, and buttons.

Create a new folder and name it as you like. We named our folder Custom.

Inside the folder, create a text file and change the extension to xml. Here, our created file is Custom_Rib.xml.

Then, open the file with Notepad.

After that, paste the following code. This code will create a custom tab. The custom tab has two groups. Both of the group contains two buttons.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id = "first_tab" label = "Custom Tab">
            <group id = "group1" label = "First Group">
                        <button id = "button1" label = "Button 1"/>
                        <button id = "button2" label = "Button 2"/>
            </group>  
            <group id = "group2" label = "Second Group">
                        <button id = "button3" label =  "Button 3"/>
                        <button id = "button4" label = "Button 4"/>
            </group>
</tab>
</tabs>
</ribbon>
</customUI>

Save the file by pressing Ctrl+S.
Finally, drag and drop the Custom folder to the zip file to complete this step.

Step 3: Edit .rels File and Add Relationship to Excel File

To customize the ribbon in Excel VBA, we will modify the .rels file to add a relationship with the Custom file to the sample Excel file.

First, copy the _rels folder from the zip file.

Next, paste it into any folder outside the zip file.

Then go to the _rels folder and open the .rels file with Notepad.

Inside the file, add a new Relationship and change the Target argument. The argument must match the folder and custom XML file name.

<Relationship Id="softeko"
Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility"
Target="/Custom/Custom_Rib.xml"/></Relationships>

Then save the .rels file.
After that, Delete the existing _rels folder inside the zip file.
Finally, drag and drop the new _rels folder in the zip file.

Now, to see the newly added tab, groups, and buttons, change the file extension of the zip file to xlsm file.

Finally, launch the xlsm file and you will find a new tab named Custom Tab that contains two groups and four buttons.

Step 4: Customize Buttons with Icon

To enhance the appearance of the buttons, we will now add icons to them. You can add built-in icons or you can add any image you want as the icon.

First, create two new folders inside the Custom folder and name them _rels and Images.

The image folder contains three image files with png extension.

In the _rels folder, create a new file and name it Custom_Rib.xml.rels.

Open the file with Notepad and paste the following code.

<?xml version="1.0" encoding="utf-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/image2.png" Id="icon_2" />
<Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/image3.png" Id="icon_3" />
<Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/image4.png" Id="icon_4" />
</Relationships>

After that, open Custom_Rib.xml file and insert the following code there.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id = "first_tab" label = "Custom Tab">
            <group id = "group1" label = "First Group">
                        <button id = "button1" label = "Button 1" imageMso = "HappyFace" size = "large" onAction = "Macro1"/>
                        <button id = "button2" label = "Button 2" image = "icon_2" size = "large" onAction = "Macro2"/>
            </group>
            <group id = "group2" label = "Second Group">
                        <button id = "button3" label =  "Button 3" image = "icon_3" onAction = "Macro3"/>
                        <button id = "button4" label = "Button 4" image = "icon_4" onAction = "Macro4"/>
            </group>
</tab>
</tabs>
</ribbon>
</customUI>

imageMso = “HappyFace” adds a built-in icon to Button 1.
image = “icon_2” adds the image that has icon_2 as Id to Button 2.

Step 5: Connect Macros to Buttons

In the last step, we will connect to the buttons. The code for connecting the macro is given below.

onAction = "Macro Name"

This onAction connection is known as Callbacks. Creating a sub-procedure using Macro Name will assign the code to the button.

After adding all the codes, save the files.
Then delete the existing Custom folder from the zip file and add the new Custom folder inside it.
Now change the extension of the zip file to xlsm and open the Excel file.
Click on Custom Tab and you will find that all the buttons have images and icons.

To test the buttons, write the following sample code in a VBA code Module. The code will work when Button 4 is clicked.

Sub Macro4(control As IRibbonControl)
 Dim myRange As Range
    On Error Resume Next
    Set myRange = Application.InputBox("Select the range:", _
    "Delete Range", Type:=8)
    On Error GoTo 0
    If Not myRange Is Nothing Then
        myRange.Delete
    End If
End Sub

Code Breakdown:

Set myRange = Application.InputBox("Select the range:", _
    "Delete Range", Type:=8)

This line takes a range in an InputBox and assigns it to myRange variable.
myRange.Delete deletes the content of myRange.
Click on Button 4 and an InputBox will pop up.
Select a range in the InputBox and as a result, the range will be deleted.

Frequently Asked Questions

1. What is the shortcut to customize ribbon?

First, press Alt+F and then press T to open the word option dialogue box. After that, press the Down Arrow key and select Customize Ribbon.

2. How do I personalize a ribbon in Excel?

Right-click on the Excel ribbon and select Customize the Ribbon. From the Ribbon tab window, you can add or remove different commands, tabs, or groups as per your need.

Things to Remember

Before dragging a folder in the zip file, delete the existing folder with the same name inside the zip file.
While changing the extension of a file, you will get a warning message. You can simply click on Yes to continue when that happens.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

Customize Ribbon.xlsm

Conclusion

In this article, we demonstrated five easy steps to create and edit Excel VBA to customize ribbon without using any third-party software. We used Notebook to create an XML file and then added a relationship to the Excel file. In this article, we have created a new custom tab that has two groups. Each group contains two buttons. Finally, we have shown how to assign macros to these buttons to make them work. Thanks for reading this article. We hope you found this article useful. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.

<< Go Back to Customize Excel Ribbon | Excel Ribbon | Excel Parts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!