Custom button in access ribbon

[buratti]

I'm in the process of learning how to create a custom ribbon bar for my DB. I figured out the basics and how to add and arrange existing buttons/commands and groups, but is there any way to add a button that runs a custom procedure? Well the custom procedure is very simple... just something that would open a specific form, or maybe something that runs VBA code. Basically I would like to transform the ribbon into a navigation pane for my DB. I would only have a handful of buttons and each one would open a separate form or report. My database design is mostly complete and I will have no more need for the standard "editing/designing" ribbon that is currently displayed. Is this something possible, or am I just stuck with the built in commands? Or even is there a built in command that does this and I just didn't see it?

Something like...
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="dbCustomTab" label="A Custom Tab" visible="true">
<group id="dbCustomGroup" label="A Custom Group">
<control whatever goes here to open the form named "Customer Detail"...>
<control different code to open the form named "Order Detail"...>
<control Etc...>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

 

[Avelino]

Hi

three thing you need to understand:

1) The ID attribute identifies a control exclusively, correct? Will be used in VBA code to identify the control clicked.

2) OnAction attribute is used to trigger the procedure in VBA

3) copy the function to a global module.

Public Sub [COLOR=green]fncOnAction[/COLOR] (control The IRibbonControl) 
On Error GoTo Trataerro 
  Select Case control.Id 
    Case "[COLOR=red]btcustomer[/COLOR]" 
      DoCmd.OpenForm "NameOfYourForm" 
    Case Else 
      MsgBox "button:" & control.Id, vbInformation, "Warning" 
  End Select 
exiterror: 
  Exit Sub
trataerror: 
  MsgBox "Error:" & Err.Number & vbCrLf & Err.Description, vbCritical,"Warning", Err.HelpFile, err.helpcontext 
  Resume exiterror: 
End Sub

now watch the construction of the button

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
<ribbon startFromScratch="true"> 
<tabs> 
<tab id="dbCustomTab" label="A Custom Tab" visible="true"> 
<group id="dbCustomGroup" label="A Custom group"> 
 
[COLOR=royalblue]<button[/COLOR]
[COLOR=royalblue]id = "[COLOR=red]btcustomer[/COLOR]"
imageMso = "CreateTableTemplatesGallery"
label = "Customers Detail"
onAction = "[COLOR=green]fncOnAction[/COLOR]"
/>[/COLOR]
 
<control different code to open the form named "Order Detail"...> 
<control Etc...> 
</ group> 
</ tab> 
</ tabs> 
</ ribbon> 
</ customUI>

Success

 

[vbaInet]

Excellent Avelino.

For no. 2, (just for brevity) I would rather say clicking the button triggers the On Action callback, which in essence calls the procedure it points to.

 

[buratti]

Ok it works kinda... I got the ribbon bar to display correctly, but something is wrong with the function or how its calling it. For testing purposes i just have one button trying to open a form. I get the following error: "Access cannot run the macro or callback function 'fncOnAction' Make sure the macro or function exists and takes the correct parameters"

I copied the function exactly as you wrote it and just changed the form name and placed it it its own module named Ribbon. Upon pasting however, the first line of the function "Public Sub fncOnAction (control The IRibbonControl)" was displayed in red indicating some type of syntax error. I tried variations of the following, but none seemed to work...

Public Sub fncOnAction (control as IRibbonControl)
Public Sub fncOnAction () '(control The IRibbonControl) thinking this was just a comment

Do I need to pass sometype of value when calling the function or what else am I doing wrong?

 

[vbaInet]

Don't use that name "Ribbon" as it's a reserved name.

Here are some excellent links:
http://www.accessribbon.de/en/index.php?Welcome
http://msdn.microsoft.com/en-us/library/aa338202.aspx

 

[Avelino]

sorry,

forgot to mention that you have to activate the reference in VBA: Microsoft Office 12.0 Object Library

 

[buratti]

Thanks... there is no error now, but when I click the button it just does nothing, no error or anything. I double checked the references, ID's and form names and they all seem correct.

Full XML:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="dbCustomTab" label="A Custom Tab" visible="true">
<group id="dbCustomGroup" label="A Custom group">
<button id = "btcustomer"
imageMso ="CreateTableTemplatesGallery"
label = "Customers Detail"
onAction = "fncOnAction" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

Full VBA:

Public Sub fncOnAction(control As IRibbonControl)
On Error GoTo Trataerro
Select Case control.ID
Case "btcustomer"
MsgBox "the button was clicked", vbOKOnly
DoCmd.OpenForm "frmCustomers"
Case Else
MsgBox "button:" & control.ID, vbInformation, "Warning"
End Select
exiterror:
Exit Sub
trataerror:
MsgBox "Error:" & Err.Number & vbCrLf & Err.Description, vbCritical, "Warning", Err.HelpFile, Err.HelpContext
Resume exiterror:
End Sub

I added the message box prompt to see if maybe I just had a simple misspelling in references, but the prompt does not show when clicking the button, nor any of the other prompts in the VBA for that matter. Anything else

 

[vbaInet]

Did you put the xml code in the USysRibbons table? And is that what you named your table?

Also, when you put XML code in the table you may need to Compress and Repair, or close and reopen your db.

 

[Avelino]

Download the sample

 

[buratti]

Ok seems to be working now. I did put it in the table USysRibbons. i was using it in a sample database I quickly created. I just copied everything to my main DB, changed a few reference names and it works. The compact and repair on the sample DID NOT work though, but it doesn't matter now, that was just a sample.

Thanks for your help with this issue. I now found a new, not error, but unexpected action by access if you would be interested in assisting me with this....

Like I stated in my first post, this ribbon bar is going to be a navigation pane for my database. I have all of my forms set to popup and my end result is to make my database "appear" as if it is a standalone application. I wanted to create the custom ribbon bar then shrink the access window to the size of the ribbon (and remove the side navigation pane displaying tables queries, etc.). What I jsut figured out is that when resizing the access window, once the bottom of the window gets to within an inch or so of the bottom of the ribbon, the ribbon (and office button) dissapear. Is there any way to "lock the ribbon to not do this?

 

[vbaInet]

Not that I'm aware of. If the ribbon hasn't got anymore space to shrink to for all the controls to be visible, then it would hide. It's just logical. The ribbon hasn't got a vertical/horizontal bar.

 

[Avelino]

I believe it is not possible.

A configuration option to disable the navigation pane and changing the extension of the database to ACCDR.

 

[Avelino]

VbaInet sorry, had not seen your response.

 

[vbaInet]

VbaInet sorry, had not seen your response.

No need to be sorry Avelino. It's a combined effort here, that's what a forum is all about You're doing well. Looks like you create alot of ribbons?

 

[Pyro]

I would like to throw in my 2C here and go back to the VBA mentioned at the top: Instead of declaring each form individually, why not create the code so that it can be used universally? e.g.:

[COLOR=black][FONT=Verdana][COLOR=#00b050][FONT=Verdana]'Callback for OnButtonPress[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Public Sub [/COLOR][/FONT][COLOR=#00b050][FONT=Verdana]OnButtonPress[/FONT][/COLOR][COLOR=black][FONT=Verdana](ctl As IRibbonControl)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]On Error GoTo OnButtonPress_Err[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]DoCmd.OpenForm "[/FONT][/COLOR][COLOR=red][FONT=Verdana]ctl.tag[/FONT][/COLOR][COLOR=black][FONT=Verdana]"  [/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]OnButtonPress_Exit: [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exit Sub[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]OnButtonPress_Err: [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox "Error:" & Err.Number & vbCrLf & Err.Description, vbCritical,"Warning"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Resume OnButtonPress_Exit[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
[/FONT][/COLOR]

Now in your XML:

[COLOR=black][FONT=Verdana]<customUI xmlns="[URL]http://schemas.microsoft.com/office/2006/01/customui[/URL]"> [/FONT][/COLOR]
[FONT=Verdana][COLOR=black]<ribbon startFromScratch="true"> [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]<tabs> [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]<tab id="dbCustomTab" label="A Custom Tab" visible="true"> [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]<group id="dbCustomGroup" label="A Custom group"> [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]<button id = "btcustomer" [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]imageMso ="CreateTableTemplatesGallery"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]label = "Customers Detail" [/COLOR][/FONT]
[FONT=Verdana][COLOR=black]onAction = "OnButtonPress" [/COLOR][/FONT]
[COLOR=black][FONT=Verdana]tag="[COLOR=#00b050][FONT=Verdana]YourFormName[/FONT][/COLOR]"/>
</group> 
</tab>
</tabs> 
</ribbon> 
</customUI>[/FONT][/COLOR]

Now you can use the same module in all your db's and only ever need to alter the XML.

 

[Avelino]

Very good Pyro

I usually use the tag to other situations.

Using the tag greatly reduces the programming for this case.

And when you have to open a report, query, calculator or a site? How is the code to open different objects? believe that the solution may be in the proper use of the tag.

 

[vbaInet]

I'm with Avelino on this one. You're not opening a group of forms, you're needing to open one form based on the click of a button. The tag is used to group controls, not to identify a control for a particular action.

I would use a tag property if I wanted to enable/disable a group of buttons for example. The concept is the same when you use the tag property on controls in a form object.