Creating custom Ribbons - Part 2

Where are the Ribbons stored?

They can be stored in three different places:

1 – In a system table, named UsysRibbons, that you must create
2 – In a common table and you can name it as you wish
3 – In a file with the extension XML, out of Access

What fields must we create at the tables?

Id - AutoNumber
RibbonName – Text50
RibbonXML – Memo

What is the difference between the system table and a common table?

In the system table UsysRibbons, ribbons are loaded in your application, with no need to program it. Their names will appear in the properties list of the objects and in the initial configuration of the application.

The figure below shows the Ribbons initial configuration, which is in the Access Options at the Current Database

Usando Access

 

They also appear at the form and report properties

Usando Access

 

How to load in the system the ribbons stored at common table (tblRibbons) and the XML file?

You can use a macro AutoExec to execute a function that will load when you start the application, the ribbons stored in the table or XML files.

See the code example that loads the ribbons in the system, stored in the table tblRibbons:

Public Function fncLoadRibbon()
Dim rsRib As DAO.Recordset
On Error GoTo fError
'-----------------------------------------------------------------
'This function loads the ribbons stored in the table tblRibbons,
'that must be called by the macro AutoExec
'
'Create the macro AutoExec, select the action RunCode
'and type the function name in the argument : fncLoadRibbon()
'------------------------------------------------------------------
Set rsRib = CurrentDb.OpenRecordset("tblRibbons", dbOpenDynaset)
Do While Not rsRib.EOF
  Application.LoadCustomUI rsRib!RibbonName, rsRib!RibbonXml
  rsRib.MoveNext
Loop
rsRib.Close
Set rsRib = Nothing

fExit:
  Exit Function
fError:
  Select Case Err.Number
    Case 3078
      MsgBox "Table not found...", vbInformation, "Warning"
    Case Else
      MsgBox "Error: " & Err.Number & vbCrLf & Err.Description, _
      vbCritical, "Warning", Err.HelpFile, Err.HelpContext
  End Select
  Resume fExit:
End Function

Now, take a look at the code example that loads the ribbons in the system, stored in the XML file:

 

Public Function fncLoadRibbonXml()
Dim f As Long
Dim strText As String
Dim strOut As String
Dim rsXml As DAO.Recordset
On Error GoTo fError

'------------------------------------------------------------------------------
'This function loads the ribbons stored in the XML file,
'that must be called by the macro AutoExec.
'
'Create the macro AutoExec, select the action RunCode
'and type the function name in the argument: fncLoadRibbonXml()
'
'Create a table named tblRibbonsXml with the fields:
'RibbonName - In this field you stores the name you want to give to the ribbon
'RibbonXml - In this field you reports the Xml file name 
'
'This example assumes that you are with the XML files in
'the same place of your Database
'
'------------------------------------------------------------------------------
f = FreeFile
Set rsXml = CurrentDb.OpenRecordset("tblRibbonsXml", , dbOpenDynaset)
Do While Not rsXml.EOF
   Open CurrentProject.Path & "\" & rsXml!RibbonXml For Input As f

   Do While Not EOF(f)
      Line Input #f, strText
      strOut = strOut & strText & vbCrLf
   Loop

   Application.LoadCustomUI rsXml!RibbonName, strOut
   strOut = ""
   strText = ""
   f = FreeFile
   rsXml.MoveNext
Loop

fExit:
   Exit Function
fError:
   Select Case Err.Number
      Case 3078
         MsgBox "Table not found...", vbInformation, "Warning"
      Case Else
         MsgBox "Error: " & Err.Number & vbCrLf & Err.Description, _
         vbCritical, "Warning", Err.HelpFile, Err.HelpContext
   End Select
   Resume fExit:
End Function

It is necessary to refer to an Office class

To access the ribbons properties through vba is necessary to add a class reference to Microsoft Office 12.0 Object Library (14.0 for Access 2010 or 15.0 for Access 2013 or 16.0 for Access 2016).

Open the VBA, and in the Tools menu click in References

Usando Access

 

Select Microsoft Office 12.0 Object Library (14.0 for Access 2010 or 15.0 for Access 2013 or 16.0 for Access 2013)

Usando Access

 

What attribute must we use to give features to the Ribbon buttons?

The attribute used is onAction. We can use it to give it a function or a macro to execute a specific action, for example, to open a form.

Here you will see the functionality of the id, which we talked about in the first class.

An example of Button control with onAction attributes:

<button
id = "btCostumers"
imageMso = "DistributionListSelectMembers"
label = "Customers"
onAction = "fncOnAction"
/>

Now, take a look at the function fncOnAction code:

 

Public Sub fncOnAction(control As IRibbonControl)

Select Case control.Id
   Case "btCustomers"
      Docmd.OpenForm "frmCustomers" 'Opens the customers form'
   Case Else
       MsgBox "You clicked the button " & control.Id, vbInformation, "Warning"
End Select

End Sub

The control.id has the value Id of the button that had been clicked, and with the SELECT we configure the right command to be applied to the added button.

Know the splitButton control:

splitButton and Button control are the most used controls and are enough for most of the projects.

See the image, a splitButton:

Usando Access

 

Below you can see the splitButton structure, and that it works with the controls MENU and BUTTON. An example of this you can see in the video class.

 

Usando Access


Video class

In this video class you will learn how to set the splitButton control, how to export the ribbons to the example application below, and how to set the function that will provide functionality to the ribbon buttons.


 


 

Links

Subjects

Article and Video 1

 
Brief presentation of MontaRibbons
Structural concept of the XML used
How to disable all the upper ribbon of the Access
Using images from the Office’s gallery
Using internal controls of the Office
Customizing the quick toolbar
Customizing the Office Button
How to create a custom ribbon
 

Article and Video 3

 
Changing the controls at runtime, using the attributes gets
How to configure the ribbon for the language exchange
How to hide / disable the buttons on the ribbon, depending on the User logged
 


Article and Video 4

Using external images (GIF, JPEG e PNG)

Article and Video 5

 

Images (GIF, JPEG, PNG e ICO) taken from the attachment type field 

Article 6

FAQ

Article 7

Combobox and Dropdown, in practice


MontaRibbons

All the details about how to purchase

 

 


5 comment(s)

Eddie   5/31/2011 10:27:37 PM

The Attachement.PictureDisp method doesn't seem to work in Access 2010.

Avelino Sampaio   6/1/2011 7:19:18 AM

Hi Eddie

I conducted tests with Access 2010 and PictureDisp working.

Enable the reference "Microsoft Office 14.0 Object Library"

You used the example of the article?

Put a STOP, above the line "set image" and run the program

...
stop
Set Image = attAnexo.PictureDisp (ImageID)
...

Gail   3/11/2013 2:28:17 PM

Very helpful for an XML/Ribbon beginner

Raquel   4/5/2014 2:18:42 AM

Great Information!

tjandra sikoko   12/14/2014 8:13:39 AM

Can your display video in English version for menu display.
Thank You


Send your comment: