Excel VBA Tutorial - Customizing Excel Ribbon to call VBA routine

 

I conducted a Excel VBA training last week. In the class, I have shown several approaches to call the VBA subroutine:

 

  • Create a button in Quick Access toolbar that can call a subroutine
  • Create a ActiveX Control button in a userform that can call a subroutine
  • Embed a Excel Control or ActiveX Control button in a Worksheet that can call a subroutine
  • Create a Workbook_Open Event to call a subroutine

However, there is one more approach i.e. to customize the Ribbon to call a subroutine.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

Let's me demonstrate in this note.

 

Creating Macro-enabled Workbook

 

1. Create a new Excel File.

 

2. Alt + F11 to open Visual Basic Editor.

 

Note: Visual Basic Editor Opens.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

3. In the Project Explorer, double-click the ThisWorkbook.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

4. Type the following VB code into the module.

 

 

Sub MyMacro(ByVal control as IRibbonControl)

   MsgBox("Hello World")

 

End Sub

 

 

5. Close and save the file as macro-enabled workbook with the extension xlsm. I name it Ribbon1.xlsm.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

Creating XML

 

6. Create a folder called customUI.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

7. Create a XML file inside the customUI folder. Name the file customUI.xml. This XML markup file is to define the ribbon with new tab, new panel and a button to call VBA routine, and will use it to replace the existing ribbon. If you do not want to replace the entire ribbon, remove startFromScratch="true" on the 2nd line.

 

Note: Please remove the space after the < tag. This is because Facebook Note does not allow xml code inside.

 

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

  < ribbon startFromScratch="true"> 

 

    < tabs> 

      < tab id="CustomTab" label="My Tab"> 

 

        < group id="SimpleControls" label="My Group"> 

          < button id="Button1" imageMso="HappyFace" size="large" 

 

            label="Large Button" 

            onAction="ThisWorkbook.MyMacro" /> 

 

        < /group> 

      < /tab> 

 

    < /tabs> 

  < /ribbon> 

 

< /customUI>

 

Changing File Extension from xlsm to zip

 

For your information, the new Excel 2007 file formats are actually XML compatible. To see that these files are really XML compatible, all you need to do is to change the extension. For example, change the name of a workbook file from, say, "Book1.xlsm" to "Book1.zip" and then you can view the compressed folders within zip file as you can do in normal .zip file. So, uncompress the zip, edit the XML file inside the zip, and then re-compress it back to zip. Then change the extension back to ".xlsm" and it will be fully functional again -- provided you got the XML editing right!

 

8. In the Window Explorer, rename the Ribbon1.xlsm to Ribbon1.zip.

 

Note: Change the file extension from xlsm to zip.

 

Note: Before.

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

Note: After.

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

9. Double-click the Ribbon1.zip file. This will open the Winzip Program showing the files and folders within the zip file.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

10. Drag the customUI folder from window into the Zip Program.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

11. Click Add when prompted with Add dialog box.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

Extracting _rels folder from Zip

 

12. In Zip Program, choose _rels folder and click Extract to the same folder.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

13. In the _rels folder, double-click .rels file to edit it.

 

14. Add the following xml code between the last <relationship></relationship> element and the   element, and then save and close the file.

 

Note: Remember to remove space after the < tag.

< Relationship Id="customUIRelID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" 

Target="customUI/customUI.xml" />

 

Note: Carefully insert the xml code. Failed to insert into correct position will cause the Excel File cannot function.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

Putting back _rels folder to Zip

 

15. Drag the _rels back to Zip Program, replacing the existing .rels file.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

16. Close the zip program.

 

17. Change the file extension  from Ribbon1.zip to Ribbon1.xlsm.

 

From zip extension ..

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

To xlsm extension..

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

18. Open the macro-enabled file in Excel 2007. The custom UI replaces the built-in Fluent UI.

 

19. Click the Large Button. This will call the VBA routine MyMacro.

 

Customizing Excel Ribbon to call VBA routine | Cemtech Training Center Puchong, Selangor Melaka

 

By Liang Ee Hang  | Google  | LinkedIn

 

Cempaka Technology Sdn Bhd

Pusat Latihan Komputer Cempaka
~Your HRDC Premiere Training Provider~
64-2, Jalan Puteri 2/2, Bandar Puteri Puchong, 47100 Puchong Selangor, Malaysia. Tel: 603-80684461
1-28, Jalan PM4, Plaza Mahkota, 75000 Melaka. Tel: 606-2835955
Instant SSL