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.
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.
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.