Nsryjdtyk

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?

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.

Thanks pbell, this is part of the solution. Issue is that this doesn't work on Office365 - see http://www.rondebruin.nl/mac/applescripttask.htm for that. When I applied that, the flip/flop works.
It also introduced another issue, regarding your suggestion to check the current status. I wrote a handler for that:
on checkRibbonStatus()
set start_time to (current date)
tell application "System Events" to tell process "Microsoft Excel"
activate
set ribbonCurrentlyVisible to (value of attribute "AXMenuItemMarkChar" of menu item 4 of menu 5 of menu bar 1) is "✓"
end tell
set end_time to (current date)
return secondsToTimeString(end_time - start_time) -- test only; final version will return ribbonCurrentlyVisible
end checkRibbonStatus

on secondsToTimeString(t)

    -- reference: MacScripter.net : Nigel Garvey : https://macscripter.net/viewtopic.php?id=34653

    -- set d to t div days

    -- set t to t mod days

    tell (1000000 + (t div hours) * 10000 + (t mod hours div minutes) * 100 + t mod minutes) as text

        set TimeString to (text 2 thru 3 & ":" & text 4 thru 5 & ":" & text 6 thru 7)

    end tell

    return TimeString as text

end secondsToTimeString

When I run this directly from AppleScript, it executes immediately. Embedded in VBA (see below) it takes 45 seconds to run!
Sub ribbonAan()
Dim ribbonStatus As String
ribbonStatus = AppleScriptTask("ribbonStatus.scpt", "checkRibbonStatus", "")
Range("Status").Value = ribbonStatus
End Sub

Any idea how to resolve this?