Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Minimize/Maximize Ribbon using VBA

  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14

    Minimize/Maximize Ribbon using VBA

    Hi,

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

    Thanx in Advance.


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-12-2023 at 05:51 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You can replace the command to toggle ribbon visibility

    Code:
    Application.SendKeys ("^{F1}")
    with this

    Code:
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & DisplayRibbon & ")"
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Thanx for this useful function.
    Working!

  5. #5
    Junior Member
    Join Date
    Apr 2013
    Posts
    1
    Rep Power
    0

    missing from sample code.... "DisplayRibbon" does not change anything

    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
    Last edited by DocAElstein; 06-11-2023 at 01:15 PM.

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Jun 2012
    Posts
    4
    Rep Power
    0
    I think to hide ribbon. Best to switch the workbook in full view mode.
    I hope it is tricky..

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    The 'Select' is necessary otherwise the arguments will work only for the first worksheet, if we pass more than 1 worksheet.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Hide Ribbon, Gridlines, Heading and FormulaBar using VBA
    By LalitPandey87 in forum Excel Help
    Replies: 7
    Last Post: 06-10-2013, 04:41 PM
  2. Populate Ribbon Controls On Load Dynamically Through VBA Variables
    By phxpoolplayer in forum Excel Ribbon and Add-Ins
    Replies: 1
    Last Post: 04-20-2013, 01:51 AM
  3. Minimize and Maximize option in Excel UserForm
    By shabbirtaibany in forum Excel Help
    Replies: 1
    Last Post: 09-23-2012, 07:05 PM
  4. How To Add A New Group In A Ribbon
    By Excel Fox in forum Excel Ribbon and Add-Ins
    Replies: 2
    Last Post: 09-16-2011, 07:57 AM
  5. Add ribbon programmatically to Excel 2010 using VBA
    By heapifyman in forum Excel Ribbon and Add-Ins
    Replies: 6
    Last Post: 07-18-2011, 09:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •