Microsoft Access VBA Macro Programming

The preceding example only provides code that will take a specific action when the ribbon

button is clicked.

You can also include code that will pass back a return value for the purposes of the title of

the group, which then allows us to alter the title of the ribbon group programmatically.

To do this, you need to paste the field RibbonXML into the Custom UI Editor. The

element for group should be altered to read

<group id="customGroup" getLabel="MyLabel">

This originally read:

<group id="customGroup" label="Contoso Tools">

Instead of specifying a static label for the group, you are now going to alter this

dynamically using the subroutine MyLabel.

In the module for this spreadsheet, insert the following code:

Sub MyLabel(control As IRibbonControl, ReturnValue As Variant)

ReturnValue = "My new title"

End Sub

The code is similar to theOnActioncode that was created, but there is now an additional

parameter calledReturnValue. This passes back a string called "My new title", which is

used as the title for the ribbon group. If you now click Contoso, you will find that the custom

group title has changed to the new text. The restriction on using this code is that it is only

called when your form is loaded in. This gives opportunities for the title to be changed

according to a certain parameter such as the day of the week, but once it has changed, it

cannot be called again without reloading the form.

Images

You can use all the images available in the add-in Office207IconsGallery on the MSDN web

site to customize your ribbon buttons. The best way to access this add-in is to go to msdn

.microsoft.com and then search for Office2007IconsGallery. This will take you straight to the

page to download this add-in.

For example, if you want to use a smiley face, use the parameter imageMso="HappyFace"

instead of imageMso="Bold".

How Can You Use VBA Code with the Ribbon

From a VBA programming angle, the big question is how can you use VBA code dynamically

with the ribbon? The answer to this, unfortunately, is that ways are fairly limited.

Using VBA, you can find out if a particular ribbon control is enabled, is visible, or has

been clicked. You can get the control’s label, screentip, or supertip (screentip and supertip

are the same thing), and you can display the control’s image. You can execute a particular

control.

138 Microsoft Access 2010 VBA Macro Programming