Excel 2010 VBA Hide ribbon tab

Hi Experts

I have an Excel 2010 addin that has a customised tab on the ribbon, I only want the tab to apply for some documents, without customising the ribbon each Excel spreadsheet I want to use it for, is there a way I can use VBA to hide a ribbon tab.
MrDavidThornAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
You certainly can.  You have to setup some callback functions so you can get a handle to the ribbon in your add-in.

Here's a great primer on the topic from Ron deBruin with good examples:
http://www.rondebruin.nl/hidevisible.htm

Please don't hesitate to ask if you have any difficulties.

Dave
0
dlmilleCommented:
I just tested it out with one of my add-ins and it works great.

Here's what I did, following Ron's example using the downloaded zip file workbook:  Hide-Display tab:

I started with my addin (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_8933-How-to-quickly-and-accurately-populate-Word-documents-with-Excel-data-charts-and-images-including-Automated-Bookmark-generation.html)

Its attachment II: Dynamic DV! v2.0 zip.

I started with that add-in and made the following modifications:

1.  I added the RibbonModule that Ron used in that workbook to my workbook.
2.  I used the CustomUI editor and made the following modifications to my XML (SEE BOLD):
<customUI onLoad = "RibbononLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">                  
<ribbon>                  
<tabs>                  
<tab id="YourCustomTabOnTheRibbon" label="ExcelToWord!" insertAfterMso="TabHome" getVisible="GetVisible" tag="MyPersonalTab" >                  
                  
<group id="Group1" label="ExcelToWord!">                  
      <button id="Button5" label="Configuration Options" onAction="showConfigurator" imageMso="AccessFormWizard" size= "normal"/>            
      <button id="Button6" label="Generate Word Bookmarks" onAction="generateWordBookmarks" imageMso="SourceControlOptions" size= "normal"/>            
      <button id="Button7" label="Update Word with Excel Data" onAction="updateWordFromExcel" imageMso="SourceControlCheckOut" size= "normal"/>            
<separator id="Separator8"/>      
      <button id="Button9" label="Name Embedded Shape/Chart" onAction="nameEmbeddedObject" imageMso="GroupTable" size= "normal"/>      
<separator id="Separator9"/>      
      <button id="Button10" label="Exit" onAction="ExcelToWord_UserTerminate" imageMso="HeaderFooterRemoveFooterWord" size= "large"/>            
                  
</group>                  
                  
</tab>                  
</tabs>                  
</ribbon>                  
</customUI>      

So, you can see, the RibbonOnLoad sub is called when the workbook is opened, and its corresponding callback function is in the RibbonModule.  Also, I already had tagged my tab as MyPersonalTab.

I saved then reloaded my addin.  I could hide the tab calling:

1.  HideEveryTab - which just hides all custom tabs,
2.  3 versions of DisplayRibbonTab - displays my custom tab with the tag: MyPersonalTab or other variations that achieve the same end.


If you have multiple tabs, and only want to hide one of them, you can use the example found in the zip called: The Different-Tab-For-Each-Worksheet.xlsm

The approach here is your XML has different tab setups for tab combinations, each having a different tag, in that way you can call RefreshRibbon with that tag combination, all the others would be hidden.

I hope this further explanation helps.

Cheers,

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrDavidThornAuthor Commented:
Sorry for the delayed response, works a treat - Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.