Minimize/Maximize Ribbon using VBA

[LalitPandey87]

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 (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!
:cool:

[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 (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.

[snb]

You are quite right; thanks for the explanation !