Change default ribbon name programatically

Question

MS Access 2013

I have created custom ribbions using XML and set the default Ribbon name under current database with in access options.

My question is, I want to create multiple custom ribbon definitions in my USysRibbons table.  Then at start up , I want to programmatically change which one is selected as the default.

Answer 1

As a general rule one should attempt to figure out how to use one main ribbon for a given user. For forms then one can well easy “set” the ribbon in the forms on-load event. However, if you designed the ribbon correct, then you should only need one - and just hide or show a few of the options you want for "managers" or say "sales reps".

However for the application wide ribbon setting, quite sure this setting does not change until a re-start.

However a really “simple” and cool way around this issue is to set the application wide ribbon, but MAKE SURE the ribbon does not in fact exist. You then in your application start-up code using the LoadcustomerUI.

Remember the setting in a form or even the global ribbon is based on the “name” you give that ribbon WHEN you load it. For automatic loads from USYSRibbons, then the column “ribbon Name” is simply used.

However as noted you CAN specify ANY ribbon name when you use LoadCustomUI - and this includes any XML  you have for existing ribbons in the USYSRibbons table!

So you might have a global ribbon setting of “MyMain”, but make sure NO such ribbon actually exists. Then in your start-up code you can grab any ribbon from the USYSRibbons table and execute a loadCustomUI on that ribbon.

Eg:

Dim strRib    as string

‘Based on some user start-up setting, we want a managers ribbon, or a sales ribbon

If Manager = True then

   strRib = dlookup("RibbonXml","USysRibbons","RibboName = 'Manager')

else

   strRib = dlookup("RibbonXml","USysRibbons","RibboName = 'Sales')

End if

loadcustomui "MyMain",strRib

Note CAREFULLY how in above when you execute LoadCusteormUI you STATE the name you want to use – in this case “MyMain”. So this “MyMain” also happens to be the global ribbon setting you set for Access on startup. But note how that ribbon does not exist until you execute LoadCustomUI.

So the “name” you use when loading the ribbon can be the global setting in Access. Note that you can ONLY load into a given name ONE time for the duration of that application setting - so this will work to change the ribbon that displays - but you have to exit the application to switch that ribbon display again using this approach.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

Leeroy,

You stated "I want to create multiple custom ribbon definitions in my USysRibbons table.  Then at start up, I want to programmatically change which one is selected as the default."

I was just wondering, if it would get you what you needed, if you simply specified in each form's design view, what ribbon you wanted each form to use, using each form's "Ribbon Name" property?  If you did it that way, there is no need to "set the default Ribbon name under current database within access options" (that can be left blank).  Likewise, if you did it this way, there is no need to "programmatically change" which ribbons are used where (because each form would have the ribbon specified in form properties).

By the way, in addition to specifying a ribbon in the "form design view", you can also specify a ribbon in the "report design view".

I suppose that the above information may be of no use to you, but I posted this, just in case.  Also, I thought maybe other readers of this thread might find this information interesting, I went ahead and posted this.

I also wanted to recommend a book, that is related to this email thread (I have quoted the info that might be needed to order this book):

"RibbonX: Customizing the Office 2007 Ribbon

by  Robert Martin, Ken Puls and Teresa Hennig

John Wiley & Sons © 2008 (696 pages) Citation

ISBN:9780470191118"

The above mentioned book can be found at various on-line sites, for very little cost to the purchaser.  One site offered this book, used, starting at about $7 (including shipping), and a new copy of the book at about $26 (including shipping).  I will assume that the reader can search the internet for this book, and pricing, so I will not provide any links here.

Answer 3

Check out Application.LoadCustomUI in the help file. You can load all ribbons. Then DoCmd.ShowToolbar shows the one you want.