Here is a handy resume if you want to make a user interface for your add-ins, like those customized for the Office Fluent Ribbon, via XML. For a full description of the format, please, take a look at MS-CUSTOMUI format spectification (a monstruosity of 553 pages in the 8.0 version).
This resume is a mashup of info from these two sites: 1 and 2.
Note: before we get hands on, it should be noted that we need to set a reference in the VBA project (in the VBA editor, Tools menu –> References) to the Microsoft Office 1x.0 Object Library (12.0 for office 2007, 14.0 for 2010 or 15.0 for 2013 or 16.0 for 2016).
Although all this menu building proccess can be done with the NotePad, is highly recomendable to use an editor like RibbonX, with you can easily build custom ribbons automatically, and you won’t have to worry about choosing relationships, changing spreadsheets to ZIP files, and manipulating controls.
The XML code fot this representation would be like this, structured in Tabs, Groups, and Controls:
Note: before we get hands on, it should be noted that we need to set a reference in the VBA project (in the VBA editor, Tools menu –> References) to the Microsoft Office 1x.0 Object Library (12.0 for office 2007, 14.0 for 2010 or 15.0 for 2013 or 16.0 for 2016).
Although all this menu building proccess can be done with the NotePad, is highly recomendable to use an editor like RibbonX, with you can easily build custom ribbons automatically, and you won’t have to worry about choosing relationships, changing spreadsheets to ZIP files, and manipulating controls.
XML Structure
The basic structure representation of the XML schema is like the one shown on the following image, taken from MontaRibbons:The XML code fot this representation would be like this, structured in Tabs, Groups, and Controls:
Where the xx in the numerations of the first line (this is the XML NameSpace line) stands for the office version we are dealing with. In the xml file should be:... ... ... 'Office 2007... 'Office 2010, Office 2013 and Office 2016
or
Use the first one if you’re using Office 2010 or earlier, and use the second one if you’re using 2013 or later. Although the first method works fine for newer versions of Excel, it just doesn’t have as many customizable features. The XML file is stored inside the XLSX/XLSM. Just rename to a ZIP file, which you can open and explore. Inside, you should see folders like _rels, docProps, and xl. For the customized Excel ribbon, you will need to add a new folder inside this ZIP file, and make some changes inside the existing _rels folder. You’re not allowed to create a new folder inside the .zip, so in order to add the folder, create a new folder outside the ZIP file (and name it, a suitable one is customUI). Inside the folder you just created, add a text file and replace .txt with .xml extension. Before the XML nameSpace line you can set the descriptor for XML, but this isn’t required. One interesting point it to set an onLoad argument in the
tag, like this:
Specifying an onLoad argument isn’t typically necessary for basic custom ribbons, but it allows the user to run a macro (in this case, the macro sControlRibbon) each time the ribbon is loaded. This is important if you must control things like whether certain buttons or controls on your user interface are invalidated. You would do this via an IRibbonUI object macro in your Excel spreadsheet, like this.Public MyRibbon As IRibbonUI Public Sub sControlRibbon(byref ribbon As IRibbonUI) Set MyRibbon = ribbon End Sub
Relations
Also, you must add relationship(s) that connects to your customUI folder. Go ahead and add this line in anywhere between the Relationship tags; preferably, just before the closing tag: Using the built-in Windows tools, you won’t be able to add or manipulate individual files inside a zipped file. Instead, you should enter the zipped Excel Ribbon.zip folder, copy the _rels folder, and paste it outside the zipped file. Now, you can edit the .rels XML file inside. Open up the .rels file inside the _rels folder using Notepad. You should see something like this:. . .
for the 2006/01 version or
if you’re using the 2009/07 version. Finally, the Target argument should match your folder name and custom XML file. Again, the Id is just a placekeeper and can be anything legal. Once you’ve added the line, save the .rels file. Next, you need to copy the _rels folder and the customUI folder (if you haven’t already done so) to the zipped file by dragging the folder to the zipped file. Before you can do that, you will need to open the ZIP file and first delete the original _rels folder. Windows won’t overwrite folders inside zipped files. Once you’ve dragged the new folders over, you can convert the .zip file back to a .xlsm by changing the file extension. When you open the file, you should have a new tab with two groups and four buttons. These buttons will have text labels, but they won’t actually do anything yet. Keep reading to see how to customize the appearance and behavior of these buttons.Controls
The most used controls are splitButton and Button controls, and they are enough for most of the projects. Other useful elements that you could face with would be how to associate images to the controls, and some other special controls like Dropdowns/ComboBox. Each control has its own attributes to be configured (there are a bunch of parameters to deal with: size, subtitle, image, visibility, status, and others). One of the obligatory attributes, in some controls, is the id, which is an exclusive identification to identify the command. Let's take a look at the XML below where these attributes are set for some buttons:Adding Your Own Images or Icons
To add your own images or icons to your custom ribbon, you’ll need to create two folders inside the customUI folder, which is the folder where we previously added the my_customUI.xml file. One of the folders is meant to hold your images, so we’ll name the folder images. In this folder, you just need to add the picture file you want to use and give each file a unique name. I typically use .png files with dimensions of 48x48, but there’s nothing magical about this. You just don’t want them too small or they’ll be blurry. The second folder should be called _rels folder. Your customUI folder should now look like this: In the _rels folder you just made, you’ll want to add one file. Pay attention now. This file should take the name of your XML file (my_customUI.xml for us) and have .rels added to the end. Thus our final filename for the sole file in this new _rels folder should be my_customUI.xml.rels. This .rels file will tell Excel how to identify the images you want to put on your ribbon. It will contain a relationships tag with the filename of each of our images and an ID we’ll use to reference these pictures. You’re file should look something like this:Public Function fncLoadRibbonXml() Dim f As Integer Dim strText As String Dim strOut As String Dim rsXml As DAO.Recordset On Error GoTo fError '------------------------------------------------------------------------------ 'This function loads the ribbons stored in the XML file ' 'Create a table named tblRibbonsXml with the fields: 'RibbonName - In this field you stores the name you want to give to the ribbon 'RibbonXml - In this field you reports the Xml file name ' 'This example assumes that you are with the XML files in 'the same place of your Database '------------------------------------------------------------------------------ f = vba.FreeFile() Set rsXml = CurrentDb.OpenRecordset("tblRibbonsXml", , dbOpenDynaset) Do While Not rsXml.EOF Open CurrentProject.Path & "\" & rsXml!RibbonXml For Input As f Do While Not EOF(f) Line Input #f, strText strOut = strOut & strText & vbCrLf Loop Application.LoadCustomUI rsXml!RibbonName, strOut strOut = "" strText = "" f = FreeFile rsXml.MoveNext Loop fExit: Exit Function fError: Select Case Err.Number Case 3078 MsgBox "Table not found...", vbInformation, "Warning" Case Else MsgBox "Error: " & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Warning", Err.HelpFile, Err.HelpContext End Select Resume fExit: End Function
What attribute must we use to give features to the Ribbon buttons? The attribute used is onAction. We can use it to give it a function or a macro to execute a specific action. Here you will see the functionality of the id, which we talked about in the first class. An example of Button control with onAction attributes:Public Sub fncOnAction(control As IRibbonControl) Select Case control.Id Case "btCustomers" Load frmCustomers 'Opens the customers form Case Else MsgBox "You clicked the button " & control.Id, vbInformation, "Warning" End Select End Sub
The control.id has the value Id of the button that had been clicked, and with the SELECT we configure the right command to be applied to the added button. For a button control, we have the following list of gets attributes: getDescription getEnabled getImage getKeytip getLabel getScreentip getShowImage getShowLabel getSize getSupertip getVisible Also, these attributes can be set through user defined functions, if used like:Public Sub fncGetVisible(byref control As IRibbonControl, ByRef visible) Select Case control.id Case "btCustomers" if user = "john" then visible = false elseif user ="carlos" then visible = true end if End Select End Sub Public Sub fncGetLabel(byref control As IRibbonControl, ByRef label) Select Case control.id Case "btCustomers" if language = "portuguese" then label = "Clientes" elseif language = "english" then label = "Customers" end if End Select End Sub
You can also customize the controls with our own images. When the ribbon is loaded for the first time, are evaluated each of the gets used and their values are loaded, as the functions of each attribute. Once loaded for this first time, the ribbon has two methods called Invalidate and InvalidateControl to reload the ribbon. The Invalidate revalidates all the controls of a ribbon, while the method InvalidateControl revalidates the control that you specify To revalidate the state of a buttons of a loaded ribbon, you just need to enter the id attribute of the control to be revalidated inside quotation marks: objRibbon.invalidateControl ("btName") To access the methods Invalidate and InvalidateControl we must do some configurations. The first one is to refer to the class "Microsoft Office 1x.0 Object Library", the second is to put the Ribbon in the cache, by a variable. In the code below, the fncRibbon, which must be in a global module, changes dynamically the ribbon:Option Compare Database Public objRibbon As IRibbonUI Public Sub fncRibbon(ribbon As IRibbonUI) On Error Resume Next 'objRibbon will be used by us to realize changes in the ribbon at runtime Set objRibbon = ribbon End Sub
To complete it you need to put the ribbon in the variable objRibbon, called by the function fncRibbon. This is done by the onLoad attribute of the tag customUI. See a part of the xml:
We can insert external images in all ribbon’s controls that allow the use of the attributes image and getImage. We use the getImage only when we need to select images at runtime; if not, we use the attribute image. Montaribbons has a folder named imagens and there you can find as example 2 files: avel.gif and feed.png, that are used at the example ribbon rblimages. When you are creating your ribbon, copy your images to the folder imagens of Montaribbons. You should then, copy them to the folder images of your project. Create the folder images, at the same place of the application – that makes programming easier and allow us to use the relative path with the property CurrentProject.Path. For the atribute image work, its necessary the use of the atribute loadimage os the tag CustomUI, that has the function of loading images fncLoadImage. Check below, the atribute loadimage in the tag customUI:... ...
Everytime the atribute image is used, it will use the function fncLoadImage See the function fncLoadImage:... ... Public Sub fncLoadImage(imageId As String, ByRef Image) On Error GoTo fError Dim strPath As String strPath = CurrentProject.Path & "\images\" If InStr(imageId, ".png") > 0 Or InStr(imageId, ".ico") > 0 Then Set Image = LoadImage(strPath & imageId) Else Set Image = LoadPicture(strPath & imageId) End If fError_Exit: Exit Sub fError: Select Case Err.Number Case 2220 MsgBox "Image " & imageId & _ " not found on the path ...", vbInformation, "Warn" Case Else MsgBox "Erro: " & Err.Number & _ vbCrLf & Err.Description, vbCritical, "Warn", _ Err.HelpFile, Err.HelpContext End Select Resume fError_Exit: End Sub
The argument imageld of the function has the name of the image of the attribute image of a control. This name must be the same as the image stored at the folder images. The argument image of the function, then, loads the image of the folder, on the control of the ribbon. Images GIF, JPEG and BMP are accepted directly on the controls (button, gallery...) of the ribbon, using the method LoadPicture of the Access. Images PNG and ICO must be turned into BMP to be loaded. This can be done by the function LoadImage, which uses APIs of the Windows to do it. MontaRibbons exports for your project these APIs in a module named mod_picture. Check the complete XML code of a Ribbon, using two buttons that load the images from the folder imagens of MontaRibbons:
As I said, we can load our images using the atribute getImage. This option is used when we need to change an image at runtime. We will use as example the same XML above, just changing the image attribute for the getimage attribute. The attribute getimage do not depends on the attribute loadimage of the tag customUI.
This way the image is defined in the function fncGetiImage. See the function below:Public Sub fncGetImage(control As IRibbonControl, ByRef Image) On Error GoTo fError Dim strPath As String Dim strImageName As String strPath = CurrentProject.Path & "\images\" Select Case control.Id Case "bt1" strImageName = "feed.png" Case "bt2" strImageName = "avel.gif" End Select If InStr(strImageName, ".png") > 0 Or InStr(strImageName, ".ico") > 0 Then Set Image = LoadImage(strPath & strImageName) Else Set Image = LoadPicture(strPath & strImageName) End If fError_Exit: Exit Sub fError: Select Case Err.Number Case 2220 MsgBox "Button Image " & control.Id & _ " not found on the path...", vbInformation, "Warn" Case Else MsgBox "Erro: " & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Warn", Err.HelpFile, Err.HelpContext End Select Resume fError_Exit: End Sub
To use external images in the ribbons, using the attributes image and getimage, we need a local folder to store those images. Nothing changes related to the XML code. What changes is the way of extracting images by the VBA code, that are now at a table. Remember that, to use the attribute image of the controls, you must use the attribute loadImage of the tag CustomUI, which calls the function fncLoadImage.
To load the images stored at a folder, we use the LoadPicture method of the Access or the function LoadImage, to use PNG and ICO images. Check the function fncLoadImage used to load the images from a folder:... ... Public Sub fncLoadImage(imageId As String, ByRef Image) Dim strPath As String strPath = CurrentProject.Path & "\images\" If InStr(imageId, ".png") > 0 Or InStr(imageId, ".ico") > 0 Then Set Image = LoadImage(strPath & imageId) Else Set Image = LoadPicture(strPath & imageId) End If End Sub
The point is: how can we extract the Attachment type Field images from a local table? We can do it in two different ways: The first way is to extract the images directly from the attachment type field of a Form that is linked with the table, using the method PictureDisp The second way is to extract the image of the attachment type field , directly from the table, to a temporary folder, using the method SaveToFile We will use both ways See the code used to load, at the ribbon, the images extracted from an Attachment type Field, of a hidden form. This form is linked to the table that contains the images. Read carefully the comments in green!Option Compare Database Dim attAnexo As Attachment Public Sub fncLoadImage(imageId As String, ByRef Image) ‘Check if the form fmImgRibbons is open. If Not CurrentProject.AllForms("frmImgRibbons").IsLoaded Then 'Open form to just read and hidden. DoCmd.OpenForm "frmImgRibbons", acNormal, , , acFormReadOnly, acHidden 'Change the attached type field image of the form to the variable attAnexo Set attAnexo = Forms("frmImgRibbons").Controls("Images") End If 'Load images JPG, BMP, or Gif 'PictureDisp extracts Attachment type Field images of the form. Set Image = attAnexo.PictureDisp(imageId) End Sub
Remember we can’t load images PNG or ICO directly in the ribbon? We still using the function LoadImage, that transforms these images in BMP. To use this function, the image must be in a local folder. The alternative is to copy the Attachment type Field image from the table to a local folder. This image, saved at a temporary folder, goes to the LoadImage function, that will use and give it to the ribbon. After this treatment, the image is deleted from the temporary folder. Check the code used to copy the Attachment type Field image from a table, to a temporary folder:Public Function fncExtractImage(strImageName As String) As String Dim strPath As String Dim rsParent As DAO.Recordset Dim rsChild As DAO.Recordset2 Dim flData As Field2 Dim flName As Field2 strPath = CurrentProject.Path & "\temp" Set rsParent = CurrentDb.OpenRecordset("tblImagesRibbons") Set rsChild = rsParent.Fields("imageRibbon").Value Set flData = rsChild.Fields("filedata") Set flName = rsChild.Fields("Filename") 'Check if the temporary folder temp exists. If not, creates it and put 'in hidden mode. If Len(Dir(strPath, vbDirectory + vbHidden) & "") = 0 Then FileSystem.MkDir (strPath) FileSystem.SetAttr strPath, vbHidden End If 'Does a loop searching for the image. Do While Not rsChild.EOF If flName.Value = strImageName Then 'Saves Attachment type Field image in the temporary folder. flData.SaveToFile (strPath) Exit Do End If rsChild.MoveNext Loop Set flName = Nothing Set flData = Nothing Set rsChild = Nothing Set rsParent = Nothing 'The function gives the name and the pacho f the saved file, that will 'be given the function LoadImage fncExtractImage = strPath & "\" & strImageName End Function
When the image is saved at the temporary folder, the function LoadImage will treat it. Observe the complete function fncLoadImage. Read carefully the comments in green.Option Compare Database Dim attAnexo As Attachment Sub fncLoadImage(imageId As String, ByRef Image) Dim strPath As String ‘Verify if the form fmImgRibbons is open. If Not CurrentProject.AllForms("frmImgRibbons").IsLoaded Then 'Open form to just read and hidden. DoCmd.OpenForm "frmImgRibbons", acNormal, , , acFormReadOnly, acHidden 'Change the attached field form variable to attAnexo Set attAnexo = Forms("frmImgRibbons").Controls("Images") End If 'Verify if the image has the extension PNG or ICO to apply the 'transformation function LoadImage If InStr(imageId, ".png") > 0 Or InStr(imageId, ".ico") > 0 Then 'Give to the variable the local and the name of the PNG or ICO image, 'saved in the temporary folder. strPath = fncExtractImage(imageId) 'Transforms the PNG or ICO image into BMP, and puts in the ribbon. Set Image = LoadImage(strPath) 'Deletes the image from the temporary folder Temp FileSystem.Kill strPath Else 'Load images JPG, BMP ou GIF Set Image = attAnexo.PictureDisp(imageId) End If End Sub
Combobox and Dropdown With these controls, whe can have list to select items. They have their own gets attributes, in order to fill the list dynamically. - Assembling a list of reports; - Assembling a list of customers, that will serve as a filter to a form. The main difference between a ComboBox control and a Dropdown control is that in the ComboBox control, you can enter a value that is present or not in the list, what is not allowed in the Dropdown. And at the programming is a small advantage in the use of the ComboBox, because it allows the direct use of the list’s value. In the Dropdown the value returned is the list’s index. But this is no obstruction to using the Dropdown. Compare the two controls in the XML code:
Note that the difference lies in the attributes that perform an action event. The OnAction attribute to the Dropdown and the attribute OnChange to the control Combobox. Let's try to dynamically fill a list, using the Dropdown control. The mechanics of filling: the attribute GetItemCount tells the Dropdown control the amount of items that will be on the list. The Dropdown control uses this information to trigger the attribute geItemLabel, the number of times needed, to get the names (labels) that will be on the list. Both the amount of items and the names that will fill the list will be captured in a table. The Description field is used to fill the list. And the order of this list will be controlled by the field idx. The first get to be triggered by the Dropdown is GetItemCount, which will capture the maximum number of items in the list that corresponds to the number of table records. Observe the fncGetItemCountDrop function.Sub fncGetItemCountDrop(control As IRibbonControl, ByRef count) ' Tell the Dropdown, by the variable count, the number of records from the ' tblListaRelatorios table, which is the maximum amount of lines from the Dropdown. count = DCount("*", "tblReportList") End Sub
And what is the use of the argument Control at the above function? It is used in the case of having more than one Dropdown control in the ribbons. See how the function is to control more than one Dropdown:Sub fncGetItemCountDrop(control As IRibbonControl, ByRef count) Select case control.id case "dd1" 'Name of a Dropdown control count = DCount("*", "NameTable") case "dd2" 'Name of Another Dropdown Control ' Tell the dropdown, through the count variable, the amount of ' tblListaRelatorios table records, which is the maximum amount of 'lines on the dropdown. count = DCount("*", "tblReportList") end select End Sub
Now that the Dropdown control knows the total amount of items that will have on the list, it will capture each of the items (label), through the attribute getItemLabel. Note the fncGetItemLabelDrop() function that the get will trigger to check the labels:Sub fncGetItemlabelDrop(control As IRibbonControl, index As Integer, ByRef label) ' Tell the Dropdown, by the label argument, the name of the stored report at the ' tblListReport table. ' idx is a unique number for each report, that has to match with the ' position (index) in the Dropdown. label = DLookup("description", "tblComboDynamic", "idx =" & index) End Sub
Our example table has four records, which was the number reported for the Dropdown control. This will pass through fncGetItemLabelDrop() function 4 times. That's right! The function is called the amount of times the length of the list! And every time the control goes over the function, the argument index is increased by 1 (iIndex + 1). Always starting from zero (0). So it's easy to load the corresponding label, just synchronize the index argument with the idx table field. To complete, let's see the onAction attribute, which will provide functionality to the Dropdown control. The function triggered by this attribute is fncOnActiondrop ().Sub fncOnActionDrop(control As IRibbonControl,selectedId As String, selectedIndex As Integer) Dim strNameReport as string 'The argument selectIndex brings the number of the item that was selected by the user strNameReport = DLookup("report", "tblReportList", "idx =" & selectedIndex) DoCmd.OpenReport strNameReport, acViewPreview 'Redo the list, cleaning the dropdown box objRibbon.InvalidateControl ("dd1") End Sub
The name of the report is captured by the DLookup () function. See that we capture the report name that corresponds to the selectedIndex number, which should coincide with the field idx. And what about the COMBOX control? Just the same, except the call attribute onChange, and that will trigger the function fncOnChangeCbx:Sub fncOnChangeCbx(control As IRibbonControl, strText As String) dim strNameReport as string ' StrText argument has the value entered or selected from the combobox. ' We use this value to filter the DLookup() function, to capture from the table ' the exact name of the report to be open. strNameReport = dlookup("report","tblReportList","description='" & strText & "'") DoCmd.OpenReport strNameReport, acViewPreview objRibbon.InvalidateControl ("cbx1") End Sub
This was a very simple case, where the table had a greatly reduced number of records and it was possible to manually renumber the field idx, which determines the order in which information from the Description field will be loaded in the list control. As for a table, with a large amount of records and dynamic, using the idx field obviously becomes impossible. The issue is solved in a relatively simple way, which is to capture the table records, sort them into the desired way and store them temporarily in the computer memory, using a variable of the Array kind. This passage of information to memory is done in the function fncGetItemCountCbx, because it is triggered before the function fncGetItemLabelCbx, which gives the names to the list. Before proceeding, understand a little about Arrays variables. Arrays are variables that consist of a collection of values, called elements of the Array. Example:Dim strNomeCliente(20) as string This instruction creates an Array of 21 elements, each one being a conventional string variable. You create 21 elements because the first element of an array is zero (0). We will store specific information on each of the elements. Example: strNameClient(0) = "Avelino Sampaio" strNameClient(1) = "Pontocom Informática" ... strNameClient(20) = "Maestro Tecnologia"
We have here the name Avelino Sampaio stored in element 0 and the name Pontocom stored in element 1. If we want to capture the name Avelino Sampaio from the variable, simply enter its element. Example:label = strNameClient(0)
We can change the amount of elements of the variable dynamically, through the ReDim instruction. This allows us to determine the exact number of elements used, which will be equal to the number of records used:reDim strNameClient(Record number of the table) as string
Pay attention to the code used, which will capture the customers' names to the variable strNomeClienteSub fncGetItemCountCbx(control As IRibbonControl, ByRef count) Dim rs As DAO.Recordset Dim strSql As String Dim j As Long ' For the combobox frmClients form, we will make two tasks: ' 1st - Inform the quantity of items in the list for the combobox. ' 2nd - store in the computer memory, the names of clients who will fill the ' list of the ComboBox control. ' This memory contents will be used in the fncGetItemLabelCbx function ' that will be triggered soon. ' Build a query of the table tblClients to obtain the records sorted ' by the client name. strSql = "SELECT cli_name FROM tblClients ORDER BY cli_name;" ' Opens query Set rs = CurrentDb.OpenRecordset(strSql) rs.MoveLast: rs.MoveFirst ' Tell the Combobox, by the argument Count, the number of items that will be used. count = rs.RecordCount ' Determines the number of elements that will be stored at the variable ReDim strNameClient(rs.RecordCount) As String ' Here its passed to the strNameClient() variable the name of customers, record by record. j = 0 Do While Not rs.EOF strNameClient(j) = rs!cli_Name j = j + 1 rs.MoveNext Loop rs.Close Set rs = Nothing End Select End Sub
Now the combobox knows how many names will have to load on the list, and go through the function fncGetItemlabelCbx the number of times required to load the names on the list. See how the function is very simple:Sub fncGetItemlabelCbx(control As IRibbonControl,index As Integer, ByRef label) ' The combobox will pass through this function the number of times equal to the number of ' records reported in the above function. And every time it come by, it will ' increasing the argument Index (index + 1) label = strNameClient(index) End Sub
Note that we are capturing the values stored in the variable strNomeCliente() and the Index argument determines the value to be captured. The capture is being performed in sequence. StrNomeCliente (0), strNomeCliente (1), strNomeCliente (2), ..., StrNomeCliente (n). The names will be sorted in alphabetical order, as determined in the query sort. How we use the value selected from the list to perform the filtering on the form ? The function fncOnChangeCbx brings, in the strText argument, the value selected on the list. With this we can use the name of the client to perform the filtering. Follow the code:Sub fncOnChangeCbx(control As IRibbonControl, strText As String) ' We use the filter method to filter the form. ' strText brings the name of the client, selected by the user. Forms!frmClients.Filter = "cli_name='" & strText & "'" Forms!frmClients.FilterOn = True ' Rewrites and updates the list of the combobox to a new search. objRibbon.InvalidateControl ("cbx1") End Sub
Steps
- Create a folder named customUI and add an xml file inside named my_customUI.xml.
- Convert your .xlsm spreadsheet to a ZIP file by adding a .zip to the end of the file name. It’ll give a warning, but that’s okay.
- Copy the _rels folder inside the ZIP file and paste it outside the ZIP file Copy the below code into the .rels file and save.
- Copy the below code into the my_customUI.xml file and save. Delete the _rels folder in the ZIP file.
- Copy both the new customUI folder and the modified _rels folder to the ZIP file by dragging the folders into the ZIP file.
- Convert the ZIP file back to a .xlsm file. Ensure you macros are callable from the buttons. Basically, just make sure they exist in a module in your spreadsheet and have the (Control As IRibbonControl) argument we talked about earlier.
Place this code in the .rels file