Hide ribbon bar on single sheet

**Nipius** · 09-01-2015, 05:21 AM

Hi there!

I am struggling with an issue I cannot find an answer for on this forum nor Google.
I would like my Excel file to hide the ribbon bar on only one sheet when I open my workbook. Is this even possible? Or is it only possible to do so for the whole workbook?

Many thanks in advance for your help!

Regards,
Thomas

**daffodil11** · 09-01-2015, 07:50 PM

Like this?

Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ActiveSheet.Name = "Sheet2" Then
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",false)"
    Else
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",true)"
    End If
End Sub

**Nipius** · 09-02-2015, 04:38 AM

Works great, daffodil11! Thanks a lot!

As I wanted to have no headings or formula bar on this sheet, I changed the code to this:

Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ActiveSheet.Name = "Talent" Then
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",false)"
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
    Else
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",true)"
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
    End If
End Sub

It seems to work, but just wanted to check whether this is the best way to go

**daffodil11** · 09-02-2015, 09:44 AM

I'd actually edit the Open event to include the exact same code as the Activate.

This way if the workbook is saved and closed on the Talent sheet, when it opens it pops with those same adjustments.

Private Sub Workbook_Open()
    If ActiveSheet.Name = "Talent" Then
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",false)"
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
    Else
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",true)"
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
    End If
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ActiveSheet.Name = "Talent" Then
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",false)"
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
    Else
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",true)"
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
    End If
End Sub

or maybe even

Private Sub Workbook_Open()
    RibbonToggle
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    RibbonToggle
End Sub

Sub RibbonToggle()
    If ActiveSheet.Name = "Talent" Then
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",false)"
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
    Else
        Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",true)"
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
    End If
End Sub

**Nipius** · 09-02-2015, 10:21 AM

Great! Works like a charm.
Thanks a ton for your help!

Hide ribbon bar on single sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Hide ribbon bar on single sheet

Re: Hide ribbon bar on single sheet

Re: Hide ribbon bar on single sheet

Re: Hide ribbon bar on single sheet

Re: Hide ribbon bar on single sheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Hide Ribbon

Hide/Unhide Ribbon by macro

Hide the Ribbon in Excel

Hide ribbon and toolbar

Code to hide Ribbon

Ribbon - Multiple xlam adding into single Ribbon tab

Hide Tab on Custom Ribbon

Ribbon - Hide the Get Started tab

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions