Software optimize

Hide the Ribbon programmatically in Access and Excel 2007

Developers don't like Office 2007 because the Ribbon is so hard to program. But if you're using Access and Excel, you can hide and display the Ribbon with a bit of help from VBA.
All of the Office 2007 applications sport the infamous Ribbon. Unfortunately for developers, the Ribbon object isn't as easy to control as its earlier counterpart, the command bar object. Access developers get a small bonus, as they can hide and show the Ribbon programmatically using the following commands, respectively: DoCmd.ShowToolbar "Ribbon", acToolbarNo DoCmd.ShowToolbar "Ribbon", acToolbarYes Most likely, you'll hide the Ribbon when you launch the database using an AutoExec macro or startup property. You could add a custom button that shows the Ribbon or simply hide and show the Ribbon as required. Excel users can use a similarly easy set of VBA statements to hide and display the Ribbon, respectively: Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

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

How you execute the code is up to you. You can attach the hide code to ThisWorksheet's Open event to hide the Ribbon when a user opens the file. Or you might execute it when you launch a user form.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

13 comments
gtangring
gtangring

When programming an application in excel 2010 where users are supposed to input data from various screens you may want to prevent them from accessing the ribbon. Put Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" as the first line in every sub which changes sheets, AND in the Private Sub Workbook_Open() event. If you want the user to be able to access the ribbon (eg to use an add on ) from a special sheet add Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" first in the sub leading to that sheet. From this sheet you also can access the develeopers tools if need be. If no sheet displays the ribbon, make sure that you can access a macro which displays the ribbon and also make all sheet tabs visible, preferrably password protected. Make sure the following code is accessible from e.g. the menu screen before adding code to the subs for accessing other sheets. Sub Show_all_sheets_and_ribbon() Application.WindowState = xlMaximized Application.DisplayFullScreen = False Dim Password As String Do Until Password = "pawo" Password = InputBox("Admin only!! - Password required!", "Password", "????") If Password = "" Then Hide_sheets Exit Sub End If Loop Application.ScreenUpdating = False Dim wsSheet As Worksheet For Each wsSheet In ActiveWorkbook.Worksheets wsSheet.Visible = xlSheetVisible Next wsSheet ActiveWindow.DisplayWorkbookTabs = True Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" Application.ScreenUpdating = True End Sub Sub Hide_sheets() Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" Application.ScreenUpdating = False Dim ws As Worksheet For Each ws In Worksheets ws.Visible = True Next ' If "Menu" is the sheet from where you start as admin For Each ws In Worksheets If ws.Name "Meny" Then ws.Visible = False Next ActiveWindow.DisplayWorkbookTabs = False Application.ScreenUpdating = True Application.DisplayFullScreen = True Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" End Sub By following this routine the user can not even double click on the top bar to display the ribbon.

naeemahmad
naeemahmad

SendKeys "^{F1}", True : Not perfered since this will not work for your application becuase once you initiate the form more than once.. it will bring the ribbon back.. it dose not know the ribbon should stay hidden.. If you wanna cheat... just set the form to activiate as a "Pop-up" form.. this will hide some form functions..ie..window control.. but will not show the "Ribbon".. ..just a thought.. I am also getting an "Variable not defined" error with the docmd function.. something is missing and it would be nice to have the complete code or reference in this thread also! Naeem xcodecorp~ "There are only 10 people who know binary, those who do and those who dont."

gplough
gplough

I used the following code in the open event of my login form (form that opens initially) SendKeys "^{F1}", True

srfine
srfine

That is great thanks, I have been looking for something like that for a long time.

david.gent
david.gent

Not the best way to do this, use the Application.SendKeys ("^{F1}") command, and add to the QAT. This toggles the Ribbon On & Off

TQ66
TQ66

I'd rather minimise it than completely hide it - I still want the menus. Trouble is while you can do that by double clicking a tab or by pressing CTRL-F1, I can't do this from Visual Basic. I can customise menus in XML, but can't see how to minimise the ribbon. Sendkeys would have been the obvious answer, but Microsoft killed that off with Vista. So the Excel dictator applications we make end up losing four rows of real estate to blank ribbon space. That's progress for you... (by Microsoft definition anyway)

dsomerv
dsomerv

When I try it I get the compile error: Variable not defined with "Ribbon" selected So I wonder if there is a missing object library reference - do you know which reference is needed for the toolbarname "Ribbon" ?

TechBender
TechBender

Thank you so much. I've been waiting for a small miracle like this. The ribbon is a blessing for the new, and a bane for the old, as it simultaneously makes itself easier to use and harder to break. The lack of easy customization is hopefully going to be remedied, but I'm certainly not holding my breath.

naeemahmad
naeemahmad

Evertime you enter the form the "ribbon" will be there at some point.

TQ66
TQ66

The same command is already available as minimise ribbon under Customise Quick Access Toolbar. It doesn't take away the ribbon, the tabs are still there, and the ribbon will expand again while you select one. But while sendkeys is flaky at best, it's disabled in Vista

jack_stockton
jack_stockton

This is a poor way to hide the ribbon in Access, removes the Office button as well...how would a user print a report when in preview mode, if the Office button is hidden? Better to just add a custom blank ribbon to your application. That way you don't have any space lost and the normal Office button is still available. When doing development, just start the app with the shift key down to get the normal ribbon back.

ssharkins
ssharkins

That's the complaint I hear most from Office 2007 users -- many of them don't mind the new ribbon as an interface tool, but it sure does play havoc with development. :(