How to create my own Custom Ribbon in excel?

Please see the following code for creating a custom excel ribbon to your workbook.

It is not as hard as you would think, as Microsoft actually developed a software for this that makes it a lot easier. It will however most likely impress your manager or the one using the excel file as it is a good way to get rid of those classic macro buttons and make it a bit more fancy.

You can download the software below. Run the .exe file to install it, and afterwards open the application.

ExcelRibbon

.zip

Download ZIP • 156KB

Once downloaded, open the tool and it will look like the below. Open your excel file with the folder icon. Then press Insert, Sample XML, Excel A Custom Tab and you will get a drafted version of your XML code.

You will now need to tweak the XML to your specifications and insert pictures which you could get from Flaticons for example. You can download the icons you want, and insert them by clicking on the expand (+) sign on your excel file in the software and after that right click and insert your pictures. My XML looks like the below print screen. Note that I removed some parts that I didn't need in my ribbon. I also remove the element insertAfterMso="TabHome" in the beginning of the XML so that my tab is always the last one in my excel view.

Before you exit the software, click on the icon with the small red arrow to make sure that it validates, and then press save.

The tweaked XML code

Result in the excel workbook

Code for my new Excel Ribbon

I am dividing this section into 2 modules in VBA. 1 module for the RibbonControl, and 1 module for the scripts that my ribboncontrol will call. Granted, I could do these in 1 module all together but I feel that is a more clean look should you have 10+ ribboncontrols.

Ribboncontrol script

Sub ExcelScript(control As IRibbonControl)
    Call SaveScript(True)
End Sub

Sub PdfScript(control As IRibbonControl)
    Call SaveScript(False)
End Sub

Please note that my Subs needs to match the names I gave them in the XML file in the picture above for the onAction tag.

In my Ribboncontrol script I "pass" a parameter, false or true, into my main script. this allows me to reuse the same script with a simple if statement for the saving part.

My Saving script looks as follows

This is basically the same as the invoice script with a slight modification.

​Sub SaveScript(SaveAsExcel As Boolean)
​
Application.ScreenUpdating = False 'Turns off screenupdating
​
Supplier_Sheet.Activate
​
'Declaring my varaibles
Dim strfolderpath As String
Dim FileName As String
Dim strPath As String
 
strfolderpath = Range("H2")

For Each Cell In Range("A3:A10") 'my range that I will look in
    If Cell <> "" Then
​
        'Moving the data to the invoice template.
        Invoice_Sheet.Range("B12,D12") = Cell
        Invoice_Sheet.Range("B13,D13") = Cell.Offset(0, 1)
        Invoice_Sheet.Range("B14,D14") = Cell.Offset(0, 2)
        Invoice_Sheet.Range("F18") = Cell.Offset(0, 3)
 
        'Our filename
        FileName = Cell
 
        'Complete filepath and name
        strPath = strfolderpath & "\" & FileName
 
        'How we will save the files.
            If SaveAsExcel = True Then 'save as excel
 
                'saves as .xlsx format
                Invoice_Sheet.Copy

                With ActiveWorkbook
                     .SaveAs FileName:=strPath & ".xlsx", FileFormat:=xlOpenXMLWorkbook
                     .Close SaveChanges:=False
                End With

            Else 'if SaveAsExcel = False, we will save as PDF.
                    Invoice_Sheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If

    End If
Next Cell

Application.ScreenUpdating = True 'Turns on screenupdating again

MsgBox ("Done")

End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.

Custom_Ribbon

.zip

Download ZIP • 59KB