Solved

Excel 2010 VBA Hide ribbon tab

Posted on 2012-03-29
3
2,512 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

632 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