VBA Code to Hide Menu Ribbon in MS Access

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 ways 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

Run Access Form VBA 
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 VBA Code to Hide Menu Ribbon in MS access, follow these below steps:

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

Excel VBA Master Tool Kit
Run Access Form VBA

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

Form Designing VBA Access

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

 

Form Designing VBA Access

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

 

Form Designing VBA Access
Form Designing VBA Access

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
Load VBA Form in Access

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

VBA Form Access for loading forms

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

Run Access Form VBA

Hope you liked this article !!

Subscribe our blog for new amazing excel tricks.

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.
Download Practice File

Click below for some more interesting tricks and learning:

Similar Posts

Excel Hyperlink: how to create, change and remove
| | |

Excel Hyperlink: how to create, change and remove

BySumit Shrimal

This tutorial shows you three easy ways to add hyperlinks in Excel. You will learn how to insert, change, and remove hyperlinks in your worksheets. It also explains how to fix links that don’t work.
Hyperlinks are often used on the internet to move between websites. In Excel, you can create links like that too. You can make a link to another cell, a different sheet, or even another workbook. You can also link to open a new Excel file or start an email message. This guide will show you how to do all of this in Excel 2016, 2013, 2010, and older versions.

VBA Code to Remove Duplicate Rows
| |

VBA Code to Remove Duplicate Rows

Byexcelsirji

Working with huge data is always exciting and challenging. From 2007 version onward, Excel is supporting more than a million rows in each worksheet. One of the common problems with huge data is “Duplicates” and the bigger problem is to identify and remove these duplicates. In this article, we will be sharing 4 ways to delete duplicate records from your data.

VBA Code to Count Cells by Color
| |

VBA Code to Count Cells by Color

Byexcelsirji

Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.

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. Required fields are marked *

Excel VBA Master Tool Kit