Solved

Excel 2010 VBA Hide ribbon tab

Posted on 2012-03-29
3
2,279 Views
Last Modified: 2012-04-05
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.
0
Comment
Question by:MrDavidThorn
  • 2
3 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37784766
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
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37785177
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
 

Author Closing Comment

by:MrDavidThorn
ID: 37811634
Sorry for the delayed response, works a treat - Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now