Hide the Ribbon

and other bits in Microsoft Access 2016 by Adrian Price

I've long suffered the inability to successfully hide the Access Ribbon. I've found code that seems to work, only to find that the ribbon reappears later after I've published the program. Other solutions hide the ribbon during development but then it reappears when I run the program from the desktop!

The only solution that works is to create a new database and import everything from the old database and then use the code shown below. It know that old .mdb databases can be Saved As as a new .accdb database but this does not allow ribbon hiding code to work.

Expand each section below to find the solution.

Create a new accdb database

An ACCDB file is a database created in Microsoft Access 2007 or later and replaces the older .mdb format

Import all items from the existing database - tables, queries, reports, code modules

Method

  1. Select External Data ribbon
  2. Click New Data Source on the left, point to From Database and choose Access - the Get External Data dialog displays
  3. Click Browse to navigate to the old database file and open it - the path appears in the File Name box
  4. Click OK

The Import Objects dialog appears. For each tab, select all and then click OK. All items from the old database will now appears in the new database file.

Add new References (to match the References in the original file)

References are shown in the visual basic editor - click on the Access background and press Alt - F11 or display the Database Tools ribbon and select the Visual Basic icon to display the visual basic editor (VBE).

In the VBE select Tools menu > References.

The References dialog shows all the external files that contain commands which are used by the program. The minimum references are:

  • Visual Basic for Applications
  • Microsoft Access 16.0 Object Library - a library of 'objects' which means a collection of code routines

You can take a picture of the References in your old database so that you can build the new references in this dialog.

Test the references are present and correct by compiling the code: Debug > Compile.

VBA Ribbon Hiding Code

In a code module, paste the following lines of code:


Private Sub HideAccessParts()'See http://www.access-programmers.co.uk/forums/showthread.php?t=97578
 Dim RibbonState As Boolean
 'CommandBars.ExecuteMso "MinimizeRibbon" 'Toggle minimise ribbon: http://www.accessribbon.de/en/index.php?FAQ:19 'RibbonState = (CommandBars("Ribbon").Controls(1).Height < 100)
 'DoCmd.NavigateTo "acNavigationCategoryObjectType" DoCmd.RunCommand acCmdWindowHide 'Hide Navigation pane - err if already hidden 'DoCmd.LockNavigationPane True DoCmd.ShowToolbar "Ribbon", acToolbarNo 'Hide ribbon
 Application.SetOption "Show Status Bar", False "Hide status bar without re-start

End Sub

The commented lines may be useful for you to explore, otherwise delete them. Now for the code that displays the Access navigation pane and ribbon:


Private Sub ShowAccessParts()
 'CommandBars("Property Sheet").Enabled = True 'Solved issue of not seeing form's Property sheet DoCmd.NavigateTo "acNavigationCategoryObjectType" DoCmd.SelectObject acTable, , True 'Show Navigation pane DoCmd.ShowToolbar "Ribbon", acToolbarYes 'Show ribbon DoCmd.RunCommand acCmdWindowUnhide
End Sub

As you know, you can step through code and watch what happens: reduce the height of the VBE window so you can see the main Access window behind it and click inside one of the code routines. Press F8 to step through each line of code.

The following code snippets don't work!


CurrentDb.Properties "AllowFullMenus", False 'Works next time program opens, displays only Home ribbon CurrentDb.Properties "AllowToolbarChanges", False CurrentDb.Properties "AllowBuiltinToolbars", False
 Doesn't hide the ribbon: Application.LoadCustomUI "NoRibbon", "<customUI xmlns=" & QUOTES & _ "http://schemas.microsoft.com/office/2006/01/customui" & QUOTES & _ "><ribbon startFromScratch=" & QUOTES & "true" & QUOTES & "></ribbon></customUI>" SendKeys "^{F1}", True 'Early nasty attempt to show/hide the ribbon DoCmd.ShowToolbar "Ribbon", acToolbarNo 'Works!