Customize Excel Ribbon - All You Need To Know
HTML-код
- Опубликовано: 23 июл 2024
- Read the post and download the files here: excelmacrofun.blogspot.com/202...
Find the list of ribbon image icons in this other page: excelmacroclass.blogspot.com/...
We can easily customize the Excel ribbon manually and hide certain tabs or add new tabs and buttons. However, the customization does not persist if we share the Excel workbook with other users; it only applies to the local copy of Microsoft Excel. We need to modify the XML code to export the customization with the file. That’s especially helpful when creating Excel add-ins. In this video we see how to customize the ribbon manually and locally, and how to modify the XML code to customize the ribbon programmatically and permanently for a given workbook.
For more Excel VBA fun applications and games visit Excel Macro Fun: excelmacrofun.blogspot.com/
And for other Excel VBA learning resources and applications visit any of the other blogs of the Excel Macro Mania saga:
Excel Macro Class (excelmacroclass.blogspot.com)
Excel Macro Business (excelmacrobusiness.blogspot.com)
Excel Macro Sports (excelmacrosports.blogspot.com)
Thank you very much for this video. It is an excellent explanation of how to manipulate the ribbon using xml. 🙏🙏
Excellent resource, thank you!
This was absolutely awesome... perfectly to the point and exactly what I needed... Thank you so very much.
Thanks for the feedback, much appreciated! It took me some time to put it all together 🙂
finally a video showing how to get / create the xml file! 🙂 so many thanks!
Else, a little note about the tool "Custom UI Editor" : it does not work with MS project files.
Thanks for sharing that info, I've used Ms Project a lot but never tried to customize the ribbon. I guess you can then try the manual method.
Using the button can you launch a new Excel file? is so can you distribute the xlam file to others with the excel file being able to be launched
Yes, the button should call a macro with something like: Workbooks.Open Filename:="C:\users\username\documents ....\excelfile.xlsx" , or probably a shared drive location or SharePoint where all users have access. If you distribute as xlam (Excel add-in) they need to add it to Excel (in any workbook) first. If you distribute as xlsm they just have it in that only file but don't need to install or rather add the add-in.
Greetings from Chile:
I really liked the explanation of your video, but I was left wondering how to leave the new tab "by default", so that it opens every time the file is executed, that is, whenever you open the file, it opens in the new Ribbon menu tab created.
Sorry for my English 😅
Saludos!
1. if ribbon tab/group is added manually, it shows for every file opened in your computer, but fails to run macro if the workbook with the macro linked to a button is not open (or if the macro is not public).
2. if ribbon tab/group is added with VBA (only possible to add to Add-Ins tab), that code can be triggered with a Workbook_Open added to the "ThisWorkbook" module (see below), but can also be set to add it permanently (in the video or blog article example is set to add it temporarily).
Private Sub Workbook_Open()
Call AddTabToRibbon
End Sub
3. if ribbon tap/group is added with XML code, it shows only for that file where the XML was modified, and it always goes with the file (you see the same ribbon tab/groups if you open the file in other computer). To make that available to all files in your system (or other systems), you need to create an Add-In out of the file where XML was modified. Then you need to install or rather simply add the Add-In in Excel. Creating the add-in is a topic for other video though 🙂
@@ExcelMacroMania Thank you very much for the answer, but I still had the question of "How to open that precise file, but always when doing so, the menu opens in the tab". I have looked for it in many places, but I have not found an answer. The modification of the Ribbon Menu was made by editing the XML.
Thank you so much. 😔
@@vectorsaeta Hello again. I am not sure I understand the question. If you follow the steps to do the XML modification, you should ALWAYS see the new tab when opening that file. It seems, that does not happen in your case? If that's the case, I do not know why it doesn't work. Please go to the blog article and download the example file (link below). When open, you should see a new tab called "Macros" that I created via XML modification. If you don't see that tab, then we have a problem, I don't know why. If you see the tab but the buttons don't work, you maybe have macros disabled.
excelmacrofun.blogspot.com/2023/03/excel-ribbon-customization.html
@@ExcelMacroMania Hello again: 😅
It works! 🙂 but I would like it to always open in that tab when I open the XLSM file. 🥺. My tab is called "Project", but as is normal in Excel, it always opens in "Start".
@@vectorsaeta Aaah ok, I think i got it now. There are a couple of options that I am aware.
The first is adding this to the XML code for that ribbon tab, so that it is added before the first tab - which is "Home" - what you referred to as "Start", may be "Inicio" in Spanish. So just add the insertBeforeMso as below:
Another possibility, and this is probably useful if you make a VBA app in Excel that does not require Excel functions (for example I used it for some games in Excel), is hiding all the tabs except your custom tab. For that you need to add the following in the XML:
Hope that helps. Mucha suerte!