Excel Hide/Show all tabs on Ribbon except custom tab

Question

How can I hide and show all the standard Excel ribbon tabs using VBA (not XML). I do not want to hide the whole ribbon (as is asked here: VBA minimize ribbon in Excel) just the tabs. I know how to use startFromScratch using XML so please do not suggest that or other XML solutions.

So far I have done an extensive Google search and looked at:

What I am saying is I have already done an extensive search and tried many things without getting a result.

I really wish it was. Unfortunately I had already been to that page as part of my google search. Thanks for trying @PortlandRunner. — Brett, Nov 14 '13 at 01:15
you have to use XML to customize the ribbon. You can make the defaults invisible, just need to know the name of the built in Tabs. e.g. hiding the **Home** tab: ` ` — PatricK, Nov 14 '13 at 03:28
@PatricK, you're saying the xml of my custom ribbon-tab can hide the default ribbon-tabs? — johny why, Nov 04 '16 at 12:08

score 11 · Accepted Answer · answered Nov 14 '13 at 05:13

How can I hide and show all the standard Excel ribbon tabs using VBA (not XML)

The answer is "YOU CAN'T".

AFAIK, you can't do that using VBA. Unfortunately VBA doesn't expose the tabs. The only options that you have are as shown in the image below

enter image description here

So you can work with the commandbar, commandbarButton, commandbarComboBox etc...

You can say that Set cbar = Application.CommandBars("Ribbon") but after that, the problem that you will face is how to get a handle for the tabs.

What you can do with the Ribbon using VBA:

Determine whether a particular control is Enabled/Visible/Pressed(Toggleboxes/CheckBoxes)
Get a control's label, screen tip, or supertip Display the image associated with a control.
Execute a particular control.

What you can't do with the Ribbon using VBA:

Determine which tab is currently selected.
Activate a particular tab.
Hide a particular tab
Add a new tab.
Add a new group to a tab.
Add a new control.
Remove/Disable/Hide a control.

You can however use XML to achieve what you want. For example

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab idMso="TabReview" visible="false" />
        </tabs>
    </ribbon>
</customUI>

But I guess you do not want to go via the XML Route.

user6620568 · Answer 2 · 2016-07-21T15:18:52.600

You can indeed hide/show ribbons using VBA. Here is an example:

<ribbon startFromScratch="false">
  <tabs>
  <!-- EXCEL BUILT-IN TABS -->
    <tab idMso="TabDeveloper" getVisible="GetVisible">
   <group idMso="GroupCode" visible="true"/>
   <group idMso="GroupAddins" visible="true"/>
   <group idMso="GroupControls" visible="true"/>
   <group idMso="GroupXml" visible="true"/>
   <group idMso="GroupModify" visible="true"/>
 </tab>
  </tabs>
</ribbon>

Setup your XML file.
Setup your VBA script. Sub GetVisible(control As IRibbonControl, ByRef MakeVisible) Select Case control.ID Case "TabDeveloper": MakeVisible = True Case "TabHome": MakeVisible = True Case "TabInsert": MakeVisible = True Case "TabPageLayoutExcel": MakeVisible = True Case "TabFormulas": MakeVisible = True Case "TabData": MakeVisible = True Case "TabReview": MakeVisible = True Case "TabView": MakeVisible = True Case "TabAddIns": MakeVisible = True Case "TabBackgroundRemoval": MakeVisible = True End Sub
Download this file for a list of Control IDs for MS Office. http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=6627

Tip: Be sure to close your Excel Workbook that includes the ribbon you are editing before editing the XML ribbon file. I have found that it sometimes erases my VBA code (I don't know why, it just does).

score 4 · Answer 3 · edited Jun 20 '20 at 09:12

To Activate a particular tab

In Microsoft Office 2010, you activate a custom tab by using the ActivateTab method of the IRibbonUI object

You use the ActivateTabMso method for built-in tabs and ActivateTabQ for tabs shared between multiple add-ins.

ActivateTabQ also requires an additional String-type parameter that specifies the namespace of the add-in

In XML specify

customUI onLoad="RibbonOnLoad" xmlns=etc

In VBA

Public gRibbonUI As IRibbonUI 

Sub RibbonOnLoad(ribbon As IRibbonUI)

   Set gRibbonUI = ribbon

End Sub

Then in code

gRibbonUI.ActivateTab "MyTabID"

gRibbonUI.ActivateTabMso "TabHome"

score 3 · Answer 4 · edited Feb 14 '18 at 12:48

3

First, open the Excel sheet which you want hide the ribbon tab on, then press Alt+F11. Insert new code module and add following code:

Private Sub hide()
    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)
End sub

edited Feb 14 '18 at 12:48

Tomaski

487
3
15

answered Dec 24 '14 at 18:50

Seshadri

31
3

I think you are mistaken on the keyboard shortcut. – JasonMArcher Dec 24 '14 at 19:42

score 1 · Answer 5 · answered Sep 29 '14 at 19:02

1

You can use on the XML like these:

< ribbon startFromScratch="true" >

check this link:

http://www.rondebruin.nl/win/s2/win012.htm

answered Sep 29 '14 at 19:02

Evert

11
1

1

thanks for your answer but I specifically said "I know how to use startFromScratch using XML so please do not suggest that". Please read questions carefully in future. – Brett Sep 29 '14 at 21:28

score 1 · Answer 6 · answered Nov 25 '16 at 01:36

Try this XML for Excel I have tested:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
    <tab idMso="TabHome" visible="false" />
    <tab idMso="TabInsert" visible="false" />
    <tab idMso="TabFormulas" visible="false" />
    <tab idMso="TabData" visible="false" />
    <tab idMso="TabReview" visible="false" />
    <tab idMso="TabView" visible="false" />
    <tab idMso="TabDeveloper" visible="false" />
</tabs>
</ribbon>
</customUI>

Result:

Excel Hide/Show all tabs on Ribbon except custom tab

6 Answers6

Linked