A well-designed custom ribbon sends important signals: this application is modern and pays attention to user experience.
In contrast the old Switchboard form sends the opposite signals: this was done without much concern for the user.
The old toolbars are only barely supported anymore.
Once you have decided to use the ribbon, there are a few more decisions to make.
The first one is how to actually build one. Your Access and VBA skills are not necessarily enough:
as you are reading about the ribbon you will run into terms like the hidden USysRibbons table,
XML, callback functions, etc. If that’s a bit intimidating,
I would recommend reviewing the Ribbon Creator at RibbonCreator.com.
If you like the challenge, you will find it’s not as hard as it seemed, once you get the pattern going.
There are several websites with tutorials and examples, including Gunter Avenius’ excellent website for all things ribbon
AccessRibbon.com and YouTube videos such as this one.
What follows is a discussion of your options for images on the ribbon.
Ribbons have built-in support for loading images from the various Office applications.
For example this ribbon is entirely created with Office images; the Work Orders image has an imageMso of
FileCompactAndRepairDatabase and the Projects image is ControlsGallery.
The complete list can be found here and here.
There are tons of them, but they are somewhat hard to work with since they are organized alphabetically,
as well as focused on Office products which may not fit your design.
The ribbon allows for the use of custom images that you found or created yourself.
You have to abide by certain size restrictions (or Access will scale them for you) but otherwise it is not difficult.
Several file formats such as BMP, JPG, and PNG are supported.
A few examples:
One of the flagship solutions for ribbon icons is IconGenerator by Axialis. The second example was built with its icons.
They are arranged in sets by topic, and support overlays. They can be exported for the ribbon and in a variety of other formats
(e.g. SVG and XAML) and sizes.
Once you have a set of icon files, you’ll need to decide how to distribute them. There are at least 3 options:
- As files, along with your database file.
- In an attachment field in a table in your database.
- As shared images (A2010 and up).
Gunter Avenius’ website has a page about User Defined Icons,
and it explains option 1.
The disadvantage of this option is that you need to distribute extra files and will have to decide
where they should live, and how to update them.
Option 2 alleviates the concern about distribution, since the files will be in the database itself,
in a table with an attachment field. They are then saved to a temp folder and loaded from there using option 1 code.
One disadvantage I can see with this option is that is must be the slowest of the options here,
since writing and reading files takes some time. It may not be noticeable though.
Option 3 is my favorite. I was challenged by a recent discussion among Access MVPs to come up with a way to never save the images to disk
but keep them in memory. I would also store them in the Image Gallery so we don’t have to use our own table,
and we can also easily re-use the images, such as here in the Form Header of a form:
All three options are implemented in the sample database.
Check the comments in the get_image function to toggle between the three options.
The first step in implementing option 3 is loading the images into the Image Gallery. Again, this works in Access 2010 or better.
On the Design tab, click on Insert Image, then use the Browse button to add the images.
The second step is telling the ribbon which function to use when it needs an image. I called it get_image.
This is done in the first line of the ribbon XML:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="ribbon_MainLoad" loadImage="get_image">
Alternatively you can call the function from the getImage attribute of each individual button, as Gunther does in the above-mentioned web page.
The third step is writing the ribbon XML so it specifies the image for each button, e.g.
The fourth and last step is writing (or in your case; copying) the get_image function.
This function must be in a Standard Module and must be Public. Its signature is:
Public Sub get_image(ByVal Image_name As String, ByRef Image)
get_image takes a string with the image name, and returns the image via the "ByRef Image" argument.
To return the image, we only need 4 lines of code in get_image:
Dim intPos As Integer
intPos = InStrRev(Image_name, ".")
Image_name = Mid$(Image_name, 1, IIf(intPos = 0, 0, intPos - 1))
Set Image = AttachmentToPicture("MSysResources", "Data", "Name", dbText, Image_name)
The incoming Image_name is something like Bonus_org_manage.bmp, and this code strips the file extension
and passes it to AttachmentToPicture, which returns the image and it is then set to the ByRef Image argument of get_image.
Some finer points
Developers who just want to implement the solution can skip this section.
For those who would like to know a bit more background information, here are a few finer points.
The images used for the ribbon are stored in the Image Gallery. What is that?
There is no magic here: just like in Option 2 where images were stored in an attachment field,
Image Gallery uses the same technique. You can see it in action if you look at the MSysResources system table:
Now you can also see why in get_image we stripped the file extension: the Name field does not contain the extension.
The reason we put it in our ribbon XML is to make the point that there are three options to load ribbon images.
Options 1 and 2 depend on filenames with extensions. A pure "option 3" implementation does not, so some optimization is possible.
I owe a HUGE debt of gratitude to Sascha Trowitzsch, the German developer and former MVP who developed the code for this critical function around 2006, well before Image Gallery was available. If you delve into it, you will see that it is very complex as it has to deal with GDI+ to support transparent images, requiring careful control of starting and stopping the GDI+ service, and a way to circumvent VBA’s difficulty to deal with OLE Interfaces such as IPicture.
I claim no significant original code here; just the (re-)discovery of how Sascha’s code can be used to further our desire to streamline the in-memory loading of ribbon images.
Regarding the copyright statement at the top of external_GDI: when asked, Sascha replied: "the copyright text inside the module does not prevent the usage of it for any purposes you’d like. It would just be nice to remain [keep] the [attribution] lines as for courtesy." That is the least we can do.
In this blog post we discussed how to store custom ribbon images in the Image Gallery and use them in the ribbon without writing them to disk.
You can download a zip file with the download package here.
It is for 32-bit version of Access only, because of the many Windows API calls.
If someone decides to change it to support 64-bit please let me know and I will be happy to post it.