Introduction

Welcome! Customizing Ribbons & Menus

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 45. In this course we will continue with customizing ribbons and menus in Access, focusing on creating custom backstage menus, setting up Quick Access Toolbars for individual forms, and building our own ribbons table instead of using system tables. We will talk about adding right-click context menus, exploring the Command Bars collection, and identifying control IDs and captions. I will show you how to add custom buttons and functions to menus, use custom images or built-in icons, and discuss loading custom ribbons on startup. This course builds directly on Developer Level 44 and assumes knowledge from previous levels.

Navigation

Prev: D45
Next: Lesson 01

Keywords

Access Developer, custom ribbons, backstage menu, quick access toolbar, context menus, right-click menu, pop up menu, command bars, custom functions, ribbon table, Application.LoadCustomUI, face id, custom button icons, useSysRibbons, hide file menu optio

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 45 brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Today is part two of my Customizing Ribbons and Menus series.

We are going to pick up where we left off and develop a 44. We are going to start by looking at creating custom backstage menus. That is this thing when you hit File. That is called the Backstage. It is the File menu.

We are going to look at setting up Custom Quick Access Toolbars. We are going to set things across the top of Access per form, so every form can have its own Quick Access Toolbar.

We will build our own ribbons table so we do not have to use that UseSysRibbons anymore. We can load custom ribbons when the database starts up.

Then we will get to the thing that everybody has been bugging me about: these guys, right-click Pop Up menus, also called Context Menus. We will learn about the Command Bars collection. We will loop through all the controls in the system. We will actually build a little tool so we can go through and loop through all the different controls and find out what their IDs and their captions are.

We will see how to add custom buttons to menus. We will assign our own custom functions to those buttons, so you can have a right-click menu with a custom function on it. We will see how to add custom pictures or use the built-in Face IDs for the button icons.

Lots of stuff today. Of course, as I mentioned before, this class follows Developer 44. This is a pretty hard prerequisite. If you have not watched Developer 44, go watch that first. I cover a lot of stuff in Developer 44 that is crucial and fundamental to understanding this stuff today. If you do not watch that class, you are not going to understand what is going on today. So this is basically a two-part series.

And of course, before that, I strongly recommend all of my Beginner, Expert, Advanced, and Developer classes, Levels 1 through 43. They are designed to be followed one after the other. Do not skip levels.

It is currently September of 2024, so I am using Access 365, which is roughly equivalent to Access 2021. 2024 is not out yet. It is due out later this year.

If you have any questions about the material covered in today's class, feel free to post them right on the page that you are on. Just scroll down to the bottom, and you will see a box that says Post a New Comment or Question. There is also an Access forum on my website, in case you have any other questions that are not necessarily related to today's class. But you have to ask them somewhere. Ask them here.

All right, let us take a closer look at what is covered in Developer 45.

In lesson 1, we are continuing our work with customizing the ribbon. We will see how to create a gallery, a custom menu on the ribbon. Then we will start working with the backstage, which is another name for the file menu. We will see how to hide print options, and hide pretty much all the other menu options on there as well, except for that home menu option. You cannot seem to get rid of that one.

In lesson 2, I am going to show you how to add your own items to a backstage tab menu. So you can add your own categories and buttons and all kinds of things right on the backstage, right on the file menu. We will do that in lesson 2.

Lesson 3 is nice and short and simple. I am going to show you how to create custom Quick Access Toolbars for specific forms or reports. It is easy to do it for the whole database. You can do that in File Options. But if you want a custom Quick Access Toolbar for each specific form, you can do that with this technique.

In lesson 4, we are going to get rid of that hidden system table called UseSysRibbons. We are not going to use it. I do not like messing with those hidden system tables at all. So we are going to create our own custom ribbon t table. Then we will use Application.LoadCustomUI to load our own ribbons in instead of relying on that system table. We will do that on startup and then I will show you how to work that into the database.

In lesson 5, we are going to start talking about what everybody has been emailing me about: right-click context menus. Those right-click pop-up menus. We are going to start working on those in this lesson.

In lesson 6, we are continuing with custom context menus. I am going to show you how to figure out the built-in button IDs. We are going to loop through all the controls. We will actually build a form where we can search for a control by name to get its ID. It will be pretty cool.

In lesson 7, we are finishing up with context menus. We are going to learn how to make custom buttons on your right-click pop-up menus. We will learn about the action event to add custom functions behind those buttons so you can click on a button and have it do whatever you want to do. We will see how to use the face IDs for the built-in icons or load pictures so you can load your own custom images in your buttons like my pretty face.

Thanks for watching.

Quiz

Q1. What is the Backstage in Microsoft Access?
A. The area that appears when you click the File menu
B. The top row of tabs in the ribbon
C. The main form window
D. The navigation pane

Q2. What is one goal of building a custom ribbons table?
A. To avoid using the UseSysRibbons system table
B. To allow only one user at a time
C. To disable VBA code
D. To permanently hide all tabs

Q3. What feature allows you to add your own functions to right-click menus?
A. Custom Context Menus
B. Property Sheets
C. Expression Builder
D. Linked Tables Manager

Q4. What is the benefit of looping through all the controls in the Command Bars collection?
A. To find their IDs and captions
B. To export data automatically
C. To apply a theme
D. To delete all records

Q5. What is the Quick Access Toolbar?
A. A customizable toolbar at the top of Access
B. The navigation pane on the left
C. The status bar at the bottom
D. The caption at the top of the form

Q6. What is the main advantage of Application.LoadCustomUI in Access?
A. It loads custom ribbons instead of using system tables
B. It saves forms automatically
C. It locks users out of the database
D. It backs up your database

Q7. What is NOT mentioned as something you can add to your custom context menus?
A. Custom button icons
B. Custom VBA functions
C. New database file types
D. Built-in face IDs

Q8. What is recommended before taking Developer 45?
A. Watching Developer 44 and all previous levels
B. Completing only Beginner Level 1
C. Skipping directly to advanced classes
D. Installing Access 2024 beta

Q9. What is constructed in Lesson 6 of Developer 45?
A. A search tool to find control IDs in context menus
B. A new report wizard
C. An SQL pass-through query
D. A form for importing Excel files

Q10. Why does Richard prefer not to use the UseSysRibbons system table?
A. He does not like editing hidden system tables
B. It is only available in Access 2024
C. It cannot be backed up
D. It causes the database to crash

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-C; 8-A; 9-A; 10-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Microsoft Access Developer Level 45. I'm Richard Rost, your instructor, and this is the second part of my series on customizing ribbons and menus in Access.

In this session, I'm continuing from where we finished in Developer 44. We'll begin by exploring how to create custom backstage menus. For those who might not know, the backstage is accessed when you go to the File menu. We'll see how to tailor what appears there.

From there, we'll learn how to set up custom Quick Access Toolbars, even making it possible to provide different toolbars for each individual form in your database, so every form can have its own set of quick actions.

I'll also show you how to create your own ribbons table. With this setup, you won't need to rely on the UseSysRibbons hidden system table anymore. Instead, we'll load custom ribbons right when your database starts up using our own process.

After that, it's time to tackle something that a lot of you have been asking about: right-click pop-up menus, also known as context menus. We will look closely at the Command Bars collection, and I'll show you how to loop through all of the controls in the system. We will build a tool that lets us examine all the different controls and identify their IDs and captions.

I will show you how to add your own buttons to these menus, even linking them to your own custom functions, so you can have a right-click menu item that performs any action you need. We will go over adding custom images to those buttons as well, whether you use built-in Face IDs or your own graphics.

There is a lot to cover in this class, so make sure that you have already completed Developer Level 44, as much of what we do here depends on what was discussed previously. In fact, Developer 44 is a pretty strict prerequisite. If you have not watched that class, please do so first or else you might find yourself lost. Remember, all of my Beginner, Expert, Advanced, and Developer classes are meant to be followed in order, from Level 1 up to 43 before this one.

I'm teaching this in September 2024, using Access 365, which has similar features to Access 2021. At this time, Access 2024 hasn't been released yet, but it's expected later this year.

If you have questions about anything covered today, please post them in the comment section at the bottom of the page. There's also an Access forum on my website for questions on other topics.

Let me give you an overview of what will be covered in Developer 45.

In lesson 1, we'll continue customizing the ribbon, including how to create a gallery - that is, a custom menu on the ribbon. Then, we'll cover the backstage and how to hide print options and most other menu items, except that stubborn home option, which can't be removed.

Lesson 2 is all about adding your own items directly to the backstage tab menu. You'll see how to include your own categories and buttons, letting you further personalize the File menu.

Lesson 3 is straightforward. I'll walk you through making custom Quick Access Toolbars for specific forms or reports. While it's easy to create a toolbar for the whole database using the File Options, this lesson will teach you how to assign different toolbars to individual forms.

In lesson 4, we'll remove reliance on the hidden UseSysRibbons system table. Instead, we'll create our own custom ribbon table and use Application.LoadCustomUI to load our ribbons. I'll show you how to work this into your database startup routine.

Lesson 5 is where we start covering right-click context menus. This is a topic I've gotten a lot of emails about. We'll see how to implement custom right-click pop-up menus.

Lesson 6 continues with context menus. I'll show you how to identify the built-in button IDs and how to loop through controls. We'll build a form that allows you to search for a control by name and get its ID.

By lesson 7, we finish up with context menus, showing you how to add custom buttons, assign your own functionality to them using the action event, and customize the menu with either built-in Face IDs or your own images for the button icons.

Everything we cover is explained step by step with practical examples. You can find a complete video tutorial with thorough instructions on all of these topics on my website at the link below.

Live long and prosper, my friends.

Topic List

Creating custom backstage menus (File menu)
Setting up custom Quick Access Toolbars per form
Building a ribbons table to avoid UseSysRibbons
Loading custom ribbons at database startup
Working with the Command Bars collection
Building a tool to loop through controls and find IDs
Adding custom buttons to right-click context menus
Assigning custom functions to context menu buttons
Adding custom pictures or Face IDs to menu buttons
Creating a gallery on the custom ribbon
Hiding print and other options from the backstage
Adding custom items to the backstage tab menu
Creating Quick Access Toolbars for specific forms
Eliminating the UseSysRibbons system table
Creating a custom ribbon table and using LoadCustomUI
Searching for control button IDs for context menus
Making custom buttons for right-click menus
Using the action event for custom button functions
Loading icons or custom images to context menu buttons

Article

Welcome to this Microsoft Access tutorial on advanced customization of Ribbons, Menus, and Context Menus. In this guide, you will learn how to create your own custom ribbon tabs, modify the Backstage view (the interface you see when you click the File menu), make Quick Access Toolbars that are specific to individual forms, and build your own right-click context menus with custom buttons and icons. These features allow you to tailor the Access interface to better suit the needs of your users and your application.

First, let us discuss customizing the ribbon itself. The ribbon is the primary menu bar at the top of Access. You can add custom tabs, buttons, or even galleries (menus with a choice of options or icons). To do this, you will need to work with XML code describing your ribbon layout and then load this custom UI into Access. For example, you might define a new ribbon that includes a custom tab with frequently used commands tailored to your business processes.

Access also gives you the ability to customize the Backstage view, which is what appears when you click the File menu. This is called the Backstage because it sits behind the main UI and contains options for saving, printing, and managing your database. Using XML, you can not only hide unwanted options like Print or Export but also add your own categories and buttons directly to the File menu. You should be aware, however, that some options, like the main Home category, cannot be hidden due to Access limitations.

If you want even more personalization, you can set up Quick Access Toolbars. You might be familiar with adding items to this toolbar for the entire application using File and Options, but you can also create toolbars that are unique to individual forms or reports. This way, whenever a specific form opens, it displays its own toolbar with actions that are relevant only to that context. To achieve this, you typically define a toolbar using XML and assign it to the form via VBA.

Many tutorials will tell you to use a hidden system table called USysRibbons to hold your ribbon XML, but it is usually safer and more flexible to create your own table for storing ribbon definitions. For example, you can create a table named RibbonT with fields for RibbonName and RibbonXML. When your application starts, use the following VBA code to load each ribbon into memory:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM RibbonT")
Do While Not rs.EOF
Application.LoadCustomUI rs!RibbonName, rs!RibbonXML
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

This lets you manage multiple ribbons more easily and avoids dealing with hidden system tables.

One of the most frequent requests from users is to customize right-click context menus. These are also known as pop-up menus. By default, Access provides its own set of context menus when you right-click on various controls, but you can override these or add your own options using the CommandBars VBA object model. This includes the ability to create brand new command bars or modify existing menus for forms, reports, or even individual controls.

For example, you can create a new right-click menu with VBA like so:

Dim cmb As CommandBar
On Error Resume Next
' Delete if it exists
CommandBars("MyPopup").Delete
On Error GoTo 0
Set cmb = CommandBars.Add(Name:="MyPopup", Position:=msoBarPopup, Temporary:=True)
cmb.Controls.Add(Type:=msoControlButton, ID:=23).Caption = "Custom Action"
cmb.Controls("Custom Action").OnAction = "=MyCustomFunction()"

To assign this menu to a control, set the control's ShortcutMenuBar property to "MyPopup". This menu will then appear when you right-click the control. You can assign any VBA function to the OnAction property so that clicking your menu item will run your own code.

It is often helpful to know the built-in control IDs that Access uses for buttons and menu options. You can use VBA to loop through all controls and display their IDs and captions in a list or form for reference. Here is a simple example you can expand on:

Dim cb As CommandBar
Dim c As CommandBarControl
For Each cb In CommandBars
For Each c In cb.Controls
Debug.Print "ID: " & c.ID & ", Caption: " & c.Caption
Next
Next

Custom context menus can include multiple items, separators, and even custom icons. To set an icon, use the FaceId property of your CommandBarControl object, or assign a custom picture.

Throughout this process, you have almost complete control over what actions appear in menus, how they are grouped, and what code runs when the user interacts with them. For pictures, you can use the built-in FaceId numbers for the standard Access icons, or load your own images if you want a unique look.

By following these techniques, you can provide your application users with a polished, professional experience that makes your database solution feel like a fully customized software product. Whether you are hiding options in the Backstage, building custom ribbons, or offering context-sensitive right-click menus with your own functions, you can make Access work the way you want. Remember to always test your custom UI thoroughly and provide a way to revert changes if users need to get back to the standard Access environment.

If you run into any specific issues, revisit the details of the VBA examples here, especially regarding loading and assigning ribbons, as well as troubleshooting CommandBars in different Access versions. With these tips, you are well on your way to mastering advanced UI customization in Microsoft Access.