Excel Hide/Show all tabs on Ribbon except custom tab

by

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.

More Related Contents:

  1. How to copy Outlook mail message into excel using VBA or Macros
  2. For Loop not fully cycling in excel VBA [duplicate]
  3. Unique Random Numbers using VBA
  4. Populate unique values into a VBA array from Excel
  5. Closing Excel Application using VBA
  6. Delete Row based on Search Key VBA
  7. How to add a button programmatically in VBA next to some sheet cell data?
  8. What are the benefits of setting objects to “Nothing”
  9. How can I kill task manager processes through VBA code?
  10. VBA: Usage of parentheses for a method
  11. Get Unicode characters with charcode values greater hex `FFFF`
  12. How do I click a link on a web page using Excel VBA?
  13. Ping IP Address with VBA code and return results in Excel
  14. VBA copy cells value and format
  15. Multiply Entire Range By Value?
  16. VBA Excel – IFERROR & VLOOKUP error
  17. Finding a workbook in one of multiple Excel instances
  18. Delay macro to allow events to finish
  19. Export multiple sheets to PDF simultaneously without using ActiveSheet or Select
  20. declaring a unicode string in vba in excel [duplicate]
  21. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  22. Change the Point Color in chart excel VBA
  23. Extract URL From Excel Hyperlink Formula
  24. What is the different between IsNull, IsEmpty, =Empty, and an empty string ie “” and why might I use variants
  25. AutoFilter – Use of SpecialCells
  26. How to detect if user select cancel InputBox VBA Excel
  27. Change HTML email body font type and size in VBA
  28. Macros Not Showing Up in Macro Table
  29. Excel telling me my blank cells aren’t blank
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment