Solved

Excel 2010 VBA Hide ribbon tab

Posted on 2012-03-29
3
2,320 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

803 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