Code to minimize Excel 2007 ribbon

**glenin** · 02-22-2009, 10:42 PM

Hi,

I would like to be able to automatically minimize the Excel 2007 ribbon on opening a particular workbook. My preference is to be able to apply that minimization to certain worksheets only, but if that's not possible then minimizing the ribbons on all sheets in the workbook will be ok.

Any assistance with a code to do this will be appreciated.

Will I also need a BeforeClose code as well to reinstate the ribbon so that it is visible when opening subsequent workbooks?

**Paul** · 02-22-2009, 10:57 PM

Hi Glenin,

In the workbook's Open and BeforeClose events, you could use:

Application.SendKeys ("^{F1}")

This simulates the user pressing CTRL+F1. (Once hides the ribbon, twice shows it again.)

**glenin** · 02-22-2009, 11:19 PM

Thanks PJoaquin,

I think be setting this up wrong (I'm quite new to vba) because I pasted the code as below to the workbook open and beforeclose subs but there's no effect on the ribbon, although the code lines to hide and unhide the formula bar and disable the "Do you want to save changes ...?" message work ok.

Private Sub Workbook_Open()
Application.DisplayFormulaBar = False
Application.SendKeys ("^{F1}")
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFormulaBar = True
Application.SendKeys ("^{F1}")
ActiveWorkbook.Saved = True
End Sub

What am I doing wrong?

**glenin** · 02-23-2009, 12:00 AM

Hi again PJoaquin,

Disregard last post - your code is working fine. Not sure what I was doing wrong first time around, but after closing & reopening excel the hide/unhide routine worked perfectly.

Thanks for your help.

Regards.

**Paul** · 02-23-2009, 12:12 AM

Unfortunately, SendKeys does not always work as planned. Another option would be to use:

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

This will completely hide the ribbon, ribbon tabs and Quick Access Toolbar though. Setting the last argument to True will un-hide everything.

Will that suffice?

**glenin** · 02-23-2009, 01:18 AM

Thanks again.

Your latest suggestion cetainly clears out the top section of the worksheet (which is what I want to achieve) and would be just perfect if I could somehow retain the quick access toolbar. Is it too much to hope for some sort of "Display Quick Access Bar = True" code which will show the quick access bar? (I'm basing this ask on the observation that if I put the DisplayFormulaBar = True line in my test code below your latest code line the formula bar remains visible).

You're probably already on to it, but I think I've worked out why your earlier suggestion appeared not to work. It seems to be dependent on the last state of the ribbon - in other words if the ribbon was visible prior to opening a workbook with the key-stroke simulation code line the ribbon would be minimized, but if excel already had the ribbom minimized the key-stroke simulation code would unhide the ribbon. Am I on the mark?