Minimize/Maximize Ribbon using VBA

Hi,

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

Thanx in Advance.:confused:


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

Hi,

Try

Code:

---

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

Code:

---

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.

You can replace the command to toggle ribbon visibility

Code:

---

Application.SendKeys ("^{F1}")

---

with this

Code:

---

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

---

Thanx for this useful function.
Working!
:cool:

missing from sample code....

"DisplayRibbon" does not change anything


there are five parameters provided to set values:

Code:

---

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

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

Code:

---

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

---

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

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

Code:

---

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

---

To avod 'select':

Code:

---

    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

---

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

You are quite right; thanks for the explanation !