VBA CODE TO HIDE MENU RIBBON IN MS ACCESS

In MS Access, there are multiple ways to protect your code or tool from un-authorized access and edits. Some developers prefers to hide MS Access ribbons from the users to prevent changes by users. Here is one of the way you can hide MS Access Ribbons, Lock the Navigation Pane and hide hidden objects using VBA code
Note: The code is tested on MS Access 2007 and 2013

Excel Tricks

Private Sub Form_Load()
    'Hide the hidden objects in the navigation pane
    Application.SetOption "Show Hidden Objects", False
    'Lock navigation pane
    DoCmd.LockNavigationPane True
    'Disable right clicks and menus
    CurrentDb.Properties("AllowFullMenus") = False
    CurrentDb.Properties("AllowShortcutMenus") = False
    CurrentDb.Properties("AllowBuiltinToolbars") = False
End Sub

To use this code in your MS Access tool, follow below steps:

1. Open an MS Access file
2. Add a new form in MS Access using Create>Form Design menu

Excel Tricks

3. Change the following properties of the form
Auto Center: Yes
Record Selectors: No
Navigation Buttons: No
Scroll Bars: Neither
Pop Up: Yes

Excel Tricks

4. Create an Event Procedure of Form’s On Load event

Excel Tricks

5. Click on ‘…’ to create the procedure in VBA screen

Excel Tricks

Excel Tips

6. Add the following code in the Load event procedure

    'Hide the hidden objects in the navigation pane
    Application.SetOption "Show Hidden Objects", False
    'Lock navigation pane
    DoCmd.LockNavigationPane True
    'Disable right clicks and menus
    CurrentDb.Properties("AllowFullMenus") = False
    CurrentDb.Properties("AllowShortcutMenus") = False
    CurrentDb.Properties("AllowBuiltinToolbars") = False

Excel Tutorial

7. Done, save your code and right click on the form then select Open

Excel Tips

8. You will notice that ribbons are still visible, don’t worry it will be hidden when you re-open the MS Access file

Excel Videos

Hope you liked this article !!

Subscribe our blog for new amazing excel tricks.

Click to below for some more interesting tricks and learning:

Please leave your valuable comments in below section:

Similar Posts

VBA Code to Re-link MS Access Link Tables

VBA Code to Re-link MS Access Link Tables

Byexcelsirji

In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location

4 Comments

  1. No use for this code since the user can click the more command menu and can select the navigation pane and others to return.

    Reply
    1. excelsirji says:

      Thanks Sanal for your response. There are many other ways also you can bring the controls back like opening the Access file using Shift key. However the code placed in the Form load event should bring the controls back to hidden mode.

      Reply
  2. ivar atterås says:

    i have tried it in access 2016, and it doesn’t work in access 2016.

    Reply

    1. Thanks for your query Ivar. You can try following code that works with MS Access 2016:

          Dim objProperty As Property
    
    'Hide the hidden objects in the navigation pane
    Application.SetOption "Show Hidden Objects", False
    'Lock navigation pane
    DoCmd.LockNavigationPane True
    
    On Error Resume Next
    'Disable right clicks and menus
    CurrentDb.Properties("AllowFullMenus") = False
    If Err.Number = 3270 Then
        Set objProperty = CurrentDb.CreateProperty("AllowFullMenus", dbBoolean, False)
        CurrentDb.Properties.Append objProperty
        Err.Clear
    End If
    CurrentDb.Properties("AllowShortcutMenus") = False
    If Err.Number = 3270 Then
        Set objProperty = CurrentDb.CreateProperty("AllowShortcutMenus", dbBoolean, False)
        CurrentDb.Properties.Append objProperty
        Err.Clear
    End If
    CurrentDb.Properties("AllowBuiltinToolbars") = False
    If Err.Number = 3270 Then
        Set objProperty = CurrentDb.CreateProperty("AllowBuiltinToolbars", dbBoolean, False)
        CurrentDb.Properties.Append objProperty
        Err.Clear
    End If
    On Error GoTo 0
      Reply

Leave a Reply

Your email address will not be published.