CODEWITHSUNDEEP
excelvbamacosapplescript
Frank Besseling
Frank Besseling

Reputation: 21

Excel for mac hide ribbon

I'm developing a VBA program on top of Excel for Mac (rev. 16.19). Because I need more room on the (laptop) screen to display results, I want to hide the ribbon when I open the workbook.

All solutions I've seen so far only work on Windows, not on Mac. I also tried Macscript to do it via Applescript (see below). This script works fine if I run it from scripteditor, but not embedded in VBA.

tell application "System Events" to tell process "Microsoft Excel"
    set frontmost to true
    keystroke "r" using {command down, option down}
end tell

In VBA it looks like this:

Sub example()
Dim toggleRibbon As String
toggleRibbon = "tell application ""System Events"" to tell process ""Microsoft Excel""" & vbNewLine & _
                            "set frontmost to true" & vbNewLine & _
                            "keystroke ""r"" using {command down, option down}" & vbNewLine & _
                         "end tell"
Debug.Print toggleRibbon 'to check format (use of double quotes, etc.)
MacScript (toggleRibbon)
End Sub

Executing this code gives an error 5 during runtime

Can anyone solve my issue?

Upvotes: 2

Views: 1540

Answers (2)

Jonas
Jonas

Reputation: 1

i have a solution witch works with the Version 16.16.27 (and maybe others)

You would put this Code in the "This Workbook" Module:

Private Sub Workbook_Open()

  Worksheets("Tabelle1").Activate

  ActiveWindow.Zoom = 120

  MsgBox "Currently there are " & ActiveWindow.VisibleRange.Rows.Count & " Rows visible. Please change the 34 in the code."

  If ActiveWindow.VisibleRange.Rows.Count < 34 Then
    
    Application.CommandBars.ExecuteMso "MinimizeRibbon"

  End If

End Sub

The command "Application.CommandBars.ExecuteMso "MinimizeRibbon" hides the ribbon if its visible and shows it when it is not visible.

Therefore there is the if-Statement. When the ribbon is shown, there are less rows visible than if its hidden.

You might have to adjust the 34 based on the message you get. Afterwards feel free to delete the MsgBox line...

I hope i could help

Best regards

Jonas

Upvotes: 0

pbell
pbell

Reputation: 3095

According to Excel dictionary the command should be "show ribbon" or "ribbon expanded". However, trying to get these properties from application document, window, workbook, basic window,...always return "missing value". I guess it is not properly handle by Microsoft for Applescript.

So work around, one more time, is to simulate user action. Script bellow simulates the click of the item 4 (="Ribbon") of the menu 5 ("View") of the main menu bar. Of course Excel must be activate (front most) before that simulation :

tell application "Microsoft Excel" to activate
tell application "System Events" to tell process "Microsoft Excel" to click menu item 4 of menu 5 of menu bar 1

This script is a flip/flop : if ribbon is visible for the window, it becomes hidden. If ribbon is hidden, it becomes visible.

Instead of flip/flop, if you want to check current value, you need to get the check mark of the menu (missing value or ✓) This can be done via :

tell application "System Events" to tell process "Microsoft Excel" to set X to (value of attribute "AXMenuItemMarkChar" of menu item 4 of menu 5 of menu bar 1) is "✓"

X is true if ribbon is visible.

Tested on Excel 2011. Menu positions may be different on other Excel versions.

Upvotes: 1

Related Questions