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.

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.

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.

​

​

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.

​

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.