VBA to Collapse Formula Bar in Excel 2021 Mac

[SanjayGMusafir]

Hi Experts
I have been trying to figure out some way to reset my Formula bar after I have completed my task(s). Looking for VBA that I can add to serve the purpose.

Found some apparent solutions, but none of them seem to be working. Got 2, but both give different errors while running.

Can you help me find a better solution to it?

Thanks

The VBA codes I got from internet are as below -

'To Collapse Formula Bar
Application.DisplayFormulaBar = True 'to Show/hide formula bar
CommandBars.ExecuteMso "MinimizeRibbon" 'Excel Gives Debug Error on this
Application.FormulaBarHeight = 3 'Excel Closes with Error if I use this

 

[SanjayGMusafir]

I was wondering why I didn't get any replies... So thought of trying the code in windows

Application.FormulaBarHeight = 3

It works perfect in Windows but for some reason gives error in Mac
Hope this information helps someone who reaches this thread...

Still, hoping someone may be able to give solution to my problem or at least explain why mac gives error while in windows it works perfectly.

 

[Jim Gordon]

My testing resulted in crash of Excel. I reported the crash.

 

[SanjayGMusafir]

My testing resulted in crash of Excel. I reported the crash.

Thanks. Hope that might help many others too.

 

[Alex Blakenburg]

Hi Sanjay, its normally you helping other people
I don't have access to a Mac, so just did some googling.
I suspect that the issue while not identical is related to the fact that Ribbon controls are not fully supported on a Mac.

ie per the link below: "Office 2016 for Mac apps don't support some of the idMSOs that Office for Windows supports."

idMSOs supported in Excel for Mac

Office VBA reference topic

learn.microsoft.com

I thought you might be able to use send keys, since you can expand & collapse that box using Ctrl+Shift+U, but it looks like that may also not be possible using VBA on a Mac.

Sorry I don't have a solution for you but hope the above helps.