Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2010 VBA Hide ribbon tab

Posted on 2012-03-29
3
Medium Priority
?
2,680 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 42

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 42

Accepted Solution

by:
dlmille earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

927 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