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.

 

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.