2

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?

Frank Besseling
  • 21
  • 3
  • Not very familiar with this stuff but your command is Applescript not VBA, so I guess VBA will not understand it. If you look for answers on here containing the word `osascript` you will see that you can run Applescript using `osascript`. Now, in VBA you can call `Shell()` so you should be able to do `Shell(XXX)` where `XXX` is either an `osascript` command or a `bash` script that contains the `osascript` command with the Applescript stuff you already have. Fairly similar example here https://stackoverflow.com/a/2198403/2836621 – Mark Setchell Nov 20 '18 at 11:08
  • In VBA for Mac you can run Applescript using the Macscript("string") command. The "string" must contain the Applescript (including the line breaks). That is what I already tried. – Frank Besseling Nov 20 '18 at 11:19
  • Maybe click `edit` under your question and add that in if you tried it. It could be that you didn't escape the double quotes correctly - so add that into your question. – Mark Setchell Nov 20 '18 at 11:28
  • Tnx Mark, I have embedded the VBA example I tried – Frank Besseling Nov 20 '18 at 12:24

2 Answers2

1

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.

pbell
  • 2,965
  • 1
  • 16
  • 13
0

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

Jonas
  • 1