qeng
asked on
VBA to Hide a specific built-in Ribbon Tab in Excel 2010
I'm able to customize the Excel 2010 Ribbon, adding custom tabs/groups and hiding built-in tabs etc. as needed by using a CustomUI xml file. I do this by starting the ribbon from scratch using:
<ribbon startFromScratch="true">
... and then adding my own custom tabs/groups in the xml file.
What I would like to do is to leave the xml so the built-in ribbon is visible, for example, using:
<ribbon startFromScratch="false">
but within the Excel application itself, use VBA code to hide (or remove) certain built-in Tabs from the Ribbon.
In other words, I'd like the VBA code to do the equivalent of:
File > Options > Customize Ribbon > [select a Tab by name, e.g. 'Insert'] > Remove
The above steps can be done manually to get the desired result but I can't seem to find the VBA code to do it with.
Using SENDKEYS doesn't seem robust enough and only takes me a few steps into the File > Options > ... sequence before I'd have to resort to sending cursor control movements like cursor down, etc..
<ribbon startFromScratch="true">
... and then adding my own custom tabs/groups in the xml file.
What I would like to do is to leave the xml so the built-in ribbon is visible, for example, using:
<ribbon startFromScratch="false">
but within the Excel application itself, use VBA code to hide (or remove) certain built-in Tabs from the Ribbon.
In other words, I'd like the VBA code to do the equivalent of:
File > Options > Customize Ribbon > [select a Tab by name, e.g. 'Insert'] > Remove
The above steps can be done manually to get the desired result but I can't seem to find the VBA code to do it with.
Using SENDKEYS doesn't seem robust enough and only takes me a few steps into the File > Options > ... sequence before I'd have to resort to sending cursor control movements like cursor down, etc..
ASKER
Neil,
Thanks for the link. I'm familiar with that reference. That's where I got my start on customizing the ribbon via xml.
I haven't been able to find (or not yet able to understand from that reference) how to manipulate the visibility of built-in ribbon tabs via VBA. I understand how to do it via xml but that isn't practical for my needs.
Ideally, I need to find a way to change the visibility of built-in ribbon tabs via VBA executed from the application itself, after loading the file.
For example, the VBA equivalent of changing built-in tab visibility from a macro:
<tab idMso="TabDeveloper" visible="true" />
<tab idMso="TabFormulas" visible="false" />
(these are of course xml statements and not vba ... just trying to illustrate)
Thanks for the link. I'm familiar with that reference. That's where I got my start on customizing the ribbon via xml.
I haven't been able to find (or not yet able to understand from that reference) how to manipulate the visibility of built-in ribbon tabs via VBA. I understand how to do it via xml but that isn't practical for my needs.
Ideally, I need to find a way to change the visibility of built-in ribbon tabs via VBA executed from the application itself, after loading the file.
For example, the VBA equivalent of changing built-in tab visibility from a macro:
<tab idMso="TabDeveloper" visible="true" />
<tab idMso="TabFormulas" visible="false" />
(these are of course xml statements and not vba ... just trying to illustrate)
ASKER
For example, I'm searching for the VBA equivalent of this code but to hide just some of the built-in Ribbon Tabs in Excel ... not the entire ribbon:
Sub Test_HideRibbon()
Application.ExecuteExcel4M
End Sub
Sub Test_HideRibbon()
Application.ExecuteExcel4M
End Sub
In the Ron de Bruin link I sent there is a set of files you can download as a zip file. One of these is called "Hide-Display-Built-In-Tab
I'm attaching it here for simplicity.
Hide-Display-Built-in-Tabs.xlsm
I'm attaching it here for simplicity.
Hide-Display-Built-in-Tabs.xlsm
ASKER
Neil,
If I'm not mistaken, that file uses the statement:
<ribbon startFromScratch="true">
to hide the built-in Tabs. I do that in my custom xml file but the problem I then encounter is how to unhide specific built-in tabs via VBA.
If we could do this, it would achieve the same thing I'm after. The trick is to 'unhide' specific built-in tabs using VBA after using <ribbon startFromScratch="true"> in the customUI file.
That's what led me to ask the question in reverse, viz. , instead of hiding all the built-in tabs, and then unhiding the ones I want ... leave then all showing and hide the ones I don't want, from VBA.
Thx a bunch for looking into this. You're not doubt a lot more familiar than I am with the syntax and subtleties. I could be staring at me and I don't recognize it yet.
If I'm not mistaken, that file uses the statement:
<ribbon startFromScratch="true">
to hide the built-in Tabs. I do that in my custom xml file but the problem I then encounter is how to unhide specific built-in tabs via VBA.
If we could do this, it would achieve the same thing I'm after. The trick is to 'unhide' specific built-in tabs using VBA after using <ribbon startFromScratch="true"> in the customUI file.
That's what led me to ask the question in reverse, viz. , instead of hiding all the built-in tabs, and then unhiding the ones I want ... leave then all showing and hide the ones I don't want, from VBA.
Thx a bunch for looking into this. You're not doubt a lot more familiar than I am with the syntax and subtleties. I could be staring at me and I don't recognize it yet.
ASKER
i'm trying in parallel to see if I can't programmatically (ie via VBA) import and activate an imported *.exportedUI customization file
(I'll first have to figure out where Excel 2010 exports a customized ribbon file to and whether I can successfully import it manually, apply it and keep the imported customized ribbon in the workbook so it can be successfully distributed to other users)
(I'll first have to figure out where Excel 2010 exports a customized ribbon file to and whether I can successfully import it manually, apply it and keep the imported customized ribbon in the workbook so it can be successfully distributed to other users)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neil,
That was my take as well. I was hoping to avoid having to map all of the built-in tabs/groups etc. to custom ones but if that is the only way then ... I'll have to grind that out.
Somewhere in my scrounging I thought I'd come across a file somewhere (maybe on one of Ron's pages) where someone was sharing their code to map all of the default built-ins for a given version of Excel.
I'm wondering though whether the *.exportedUI customization file isn't a quicker way of getting there ... ie modify a default ribbon to show/hide the desired tabs/groups and export it say as customribbon1.exportedUI. Then ideally Import customribbon1 using VBA into the target file to achieve the desired custom Ribbon. Not sure how to do that yet but I'm guessing it can at least be done manually. Not sure if VBA has the hooks to do it programmatically.
That was my take as well. I was hoping to avoid having to map all of the built-in tabs/groups etc. to custom ones but if that is the only way then ... I'll have to grind that out.
Somewhere in my scrounging I thought I'd come across a file somewhere (maybe on one of Ron's pages) where someone was sharing their code to map all of the default built-ins for a given version of Excel.
I'm wondering though whether the *.exportedUI customization file isn't a quicker way of getting there ... ie modify a default ribbon to show/hide the desired tabs/groups and export it say as customribbon1.exportedUI. Then ideally Import customribbon1 using VBA into the target file to achieve the desired custom Ribbon. Not sure how to do that yet but I'm guessing it can at least be done manually. Not sure if VBA has the hooks to do it programmatically.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Accept: Neil Fleming (https:#a42185883)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Accept: Neil Fleming (https:#a42185883)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
This may help, for instance:
https://www.rondebruin.nl/