Friday 27 February 2015

Hide or Show an Excel Ribbon - VBA

This post demonstrates how to hide or show an excel ribbon using VBA (Visual Basic for Applications). In certain cases it may not required to show an excel ribbon to the user. In such cases, hide the excel ribbon for a better user interface.

excel ribbon  
The excel ribbon will be hidden at the application level. This means in all workbooks, the excel remains hidden.
So it is important to show the excel ribbon when the workbook is deactivated or before exit from the workbook.


Follow these four steps to handle the excel ribbon -


1. Hide ribbon when user opens the excel workbook
2. Show ribbon when user deactivates the workbook
3. Hide ribbon when user activates the workbook
4. Show ribbon before exit from the excel workbook


Hide ribbon when excel workbook is open -


Add the following code in the Workbook_Open event.
ShowRibbon is the sub-routine which shows/hides the ribbon depending on the argument passed.
False - To hide the ribbon
True - To show the ribbon

Private Sub Workbook_Open()
   ShowRibbon False
End Sub
 


Show ribbon when workbook is deactivated -


Add the following code in the Workbook_Deactivate event.
Private Sub Workbook_Deactivate()
   ShowRibbon True
End Sub
 


Hide ribbon when workbook is activated -


Add the following code in the Workbook_Activate event.
Private Sub Workbook_Activate() 
   ShowRibbon False 
End Sub
 


Show ribbon before exit the workbook -


Add the following code in the Workbook_Close event.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ShowRibbon True
End Sub
  And finally add this sub-routine in a module.


ShowRibbon -


'Show/Hide ribbon
Public Sub ShowRibbon( _
    ByVal Visible As Boolean _
)

 #If Not Authoring Then
    If Visible Then
        'show ribbon
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Else
        'hide ribbon        
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    End If
 #End If

End Sub
 

Please share your thoughts on this post in the comments section.

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.

9 comments:

  1. How can I show only tabs?

    ReplyDelete
    Replies
    1. In the following article it is clearly explained how to do that.
      https://www.rondebruin.nl/win/s2/win012.htm

      Delete
  2. Thanks for sharing this VBA code.
    If I want to compare 2 strings in EXCEL and some words which are there in 1st string which are not typed in 2nd string by mistake. How can I show them by changing the font color of the omitted words in BLUE. Is there any VBA to show omitted words.
    Regards, and Once again thanks for sharing this one.

    ReplyDelete
    Replies
    1. Hi Nilesh,
      You can format in a different color if any words are omitted by giving the start and end position of word like below -

      Consider if you want to set the color of first word which is four characters long -

      With ActiveCell.Characters(Start:=1, Length:=4).Font
      .Name = "Arial"
      .FontStyle = "Regular"
      .Size = 11
      .Strikethrough = False
      .Superscript = False
      .Subscript = False
      .OutlineFont = False
      .Shadow = False
      .Underline = xlUnderlineStyleNone
      .Color = -16777024
      .TintAndShade = 0
      .ThemeFont = xlThemeFontNone
      End With

      Delete
  3. Hi Karthik,

    Thanks for sharing your knowledge. Is it possible for you to share a code which enables user to hide ribbon only for a single worksheet in a single workbook i.e. the ribbon remains hidden for the main menu sheet and becomes visible when all other sheets become visible after hitting command button (I have already created a separate code for visibility of worksheets).

    Just for your information, I am using excel in mac and I have tried the following code in Workbook_Open event in ThisWorkbook part of vba, however it is not working for me.

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

    Thanks in advance for your help!

    ReplyDelete
    Replies
    1. Hi,

      To show only on particular worksheet you must use the worksheet activate event and call the macro inside the event with a true or false like below -

      If you activate the worksheet other than main sheet write the following code in the respective worksheet -

      Private Sub Worksheet_Activate()

      Application.ExecuteExcel4Macro "SHOW.TOOLBAR("Ribbon",True)

      End Sub


      Delete
  4. Hi Karthik,

    Thanks for your feedback. Is it possible for you to share you email address so that I can ask follow up questions, if you don't mind.

    ReplyDelete
    Replies
    1. Here I can't share my email id. Please connect on Google plus and let me know your questions.
      Regards,
      Karthik

      Delete
  5. You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this... hide wp login url

    ReplyDelete

 
biz.