Custom button in access ribbon (1 Viewer)

B

buratti

Registered User.
Local time
Today, 00:24
Joined
Jul 8, 2009
Messages
234
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

Avelino

Registered User.
Local time
Today, 01:24
Joined
Dec 10, 2009
Messages
56
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.

Code: 
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

Code: 
<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
 
Last edited:
V

vbaInet

AWF VIP
Local time
Today, 05:24
Joined
Jan 22, 2010
Messages
26,374
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.
 
B

buratti

Registered User.
Local time
Today, 00:24
Joined
Jul 8, 2009
Messages
234
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?
 
Avelino

Avelino

Registered User.
Local time
Today, 01:24
Joined
Dec 10, 2009
Messages
56
sorry,

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

buratti

Registered User.
Local time
Today, 00:24
Joined
Jul 8, 2009
Messages
234
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
 
V

vbaInet

AWF VIP
Local time
Today, 05:24
Joined
Jan 22, 2010
Messages
26,374
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

Avelino

Registered User.
Local time
Today, 01:24
Joined
Dec 10, 2009
Messages
56
Download the sample
 

Attachments

  • buratti.zip
    16.5 KB · Views: 1,471
B

buratti

Registered User.
Local time
Today, 00:24
Joined
Jul 8, 2009
Messages
234
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?
 
V

vbaInet

AWF VIP
Local time
Today, 05:24
Joined
Jan 22, 2010
Messages
26,374
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

Avelino

Registered User.
Local time
Today, 01:24
Joined
Dec 10, 2009
Messages
56
I believe it is not possible.

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

Avelino

Registered User.
Local time
Today, 01:24
Joined
Dec 10, 2009
Messages
56
VbaInet sorry, had not seen your response.
 
V

vbaInet

AWF VIP
Local time
Today, 05:24
Joined
Jan 22, 2010
Messages
26,374
Avelino said:
VbaInet sorry, had not seen your response.
Click to expand...
:)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

Pyro

Too busy to comment
Local time
Today, 14:24
Joined
Apr 2, 2009
Messages
126
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.:

Code: 
[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:

Code: 
[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

Avelino

Registered User.
Local time
Today, 01:24
Joined
Dec 10, 2009
Messages
56
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.
 
V

vbaInet

AWF VIP
Local time
Today, 05:24
Joined
Jan 22, 2010
Messages
26,374
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.
 
You must log in or register to reply here.

Similar threads

zelarra821
Add a button to correct spelling to a custom ribbon
Replies
3
Views
261
zelarra821
zelarra821
D
Can you remove groups from PrintPreview ribbon?
Replies
10
Views
536
David Anderson
D
zelarra821
Solved Create a keyboard shortcut on a ribbon
Replies
7
Views
785
zelarra821
zelarra821
D
Can I hide this button in Access 365?
Replies
3
Views
287
KitaYama
K
C
Correct implemention of Form ContextTabs and Ribbons
Replies
8
Views
1,013
cosmarchy
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom