Minimize/Maximize Ribbon using VBA

[LalitPandey87]

Hi,

I need a code which hide Ribbon, Gridlines, Heading and FormulaBar using VBA.

Thanx in Advance.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Admin]

Hi,

Try

Sub ToggleProperties(ByVal ShowRowColHeads As Boolean, _
                                    ByVal ShowSheetTab As Boolean, _
                                    ByVal ShowGridLines As Boolean, _
                                    ByVal DisplayFBar As Boolean, _
                                    ByVal DisplayRibbon As Boolean, _
                                    ParamArray ShtNames() As Variant)
                                    
    If Not IsMissing(ShtNames) Then
        ThisWorkbook.Worksheets(ShtNames).Select
        ActiveWindow.DisplayHeadings = ShowRowColHeads
        ActiveWindow.DisplayWorkbookTabs = ShowSheetTab
        ActiveWindow.DisplayGridlines = ShowGridLines
        Application.DisplayFormulaBar = DisplayFBar
        Application.SendKeys ("^{F1}")
        ThisWorkbook.Worksheets(ShtNames(0)).Select
    End If
    
End Sub

and call the routine

Sub kTest()
    
    ToggleProperties False, False, False, False, False, "Sheet1", "Sheet2"

End Sub

Note: Minimising Ribbon command toggles the current status of the Ribbon. If the Ribbon is minimised, it'll maximise and vice versa.

[Excel Fox]

You can replace the command to toggle ribbon visibility

Application.SendKeys ("^{F1}")

with this

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & DisplayRibbon & ")"

[LalitPandey87]

Thanx for this useful function.
Working!

[howard_nyc@yahoo.com]

missing from sample code....

"DisplayRibbon" does not change anything


there are five parameters provided to set values:

ShowRowColHeads As Boolean,
    ShowSheetTab As Boolean, _
    ShowGridLines As Boolean, _
    DisplayFBar As Boolean, _
    DisplayRibbon As Boolean


only four are utilized
        ActiveWindow.DisplayHeadings = ShowRowColHeads
        ActiveWindow.DisplayWorkbookTabs = ShowSheetTab
        ActiveWindow.DisplayGridlines = ShowGridLines
        Application.DisplayFormulaBar = DisplayFBar

please advise of corrected code sample

FYI: thread rating of "BAD" due to incomplete sample code... and worst of all misses the question of how to manipulate "ribbon"




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Excel Fox]

It is easily understood from my post above that you should REPLACE one line with another.

Sub ToggleProperties(ByVal ShowRowColHeads As Boolean, _
                                    ByVal ShowSheetTab As Boolean, _
                                    ByVal ShowGridLines As Boolean, _
                                    ByVal DisplayFBar As Boolean, _
                                    ByVal DisplayRibbon As Boolean, _
                                    ParamArray ShtNames() As Variant)
                                    
    If Not IsMissing(ShtNames) Then
        ThisWorkbook.Worksheets(ShtNames).Select
        ActiveWindow.DisplayHeadings = ShowRowColHeads
        ActiveWindow.DisplayWorkbookTabs = ShowSheetTab
        ActiveWindow.DisplayGridlines = ShowGridLines
        Application.DisplayFormulaBar = DisplayFBar
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & DisplayRibbon & ")"
        ThisWorkbook.Worksheets(ShtNames(0)).Select
    End If
    
End Sub

[superman]

I think to hide ribbon. Best to switch the workbook in full view mode.
I hope it is tricky..

[Admin]

Here is a non sendkeys method to minimize/maximize Ribbon (works XL 2010)

Option Explicit

Sub ToggleProperties(ByVal ShowRowColHeads As Boolean, _
                                    ByVal ShowSheetTab As Boolean, _
                                    ByVal ShowGridLines As Boolean, _
                                    ByVal DisplayFBar As Boolean, _
                                    ByVal DisplayRibbon As Boolean, _
                                    ParamArray ShtNames() As Variant)
                                        
    Const RibbonHeightSafeSide = 100    'adjust this if necessary
                                        
    If Not IsMissing(ShtNames) Then
        ThisWorkbook.Worksheets(ShtNames).Select
        ActiveWindow.DisplayHeadings = ShowRowColHeads
        ActiveWindow.DisplayWorkbookTabs = ShowSheetTab
        ActiveWindow.DisplayGridlines = ShowGridLines
        Application.DisplayFormulaBar = DisplayFBar
        If DisplayRibbon Then
            If Application.CommandBars.Item("Ribbon").Height < RibbonHeightSafeSide Then
                Application.CommandBars.ExecuteMso "MinimizeRibbon"
                'or use send keys
                'Application.SendKeys ("^{F1}")
                DoEvents
            End If
        ElseIf Application.CommandBars.Item("Ribbon").Height > RibbonHeightSafeSide Then
            Application.CommandBars.ExecuteMso "MinimizeRibbon"            
            'or use send keys
            'Application.SendKeys ("^{F1}")
            DoEvents
        End If
        ThisWorkbook.Worksheets(ShtNames(0)).Select
    End If
    
End Sub

[snb]

To avod 'select':

     If Not IsMissing(c00) Then
        With Windows(c00)
            .DisplayHeadings = Not .DisplayHeadings
            .DisplayWorkbookTabs = Not .DisplayWorkbookTabs
            .DisplayGridlines = Not .DisplayGridlines
        End With
        Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
      End If

[Admin]

The 'Select' is necessary otherwise the arguments will work only for the first worksheet, if we pass more than 1 worksheet.