I have been looking for a way to build a dynamic menu item on a ribbon that shows whatever happens to be open a the time to make it easier for the users to switch between open forms and reports. I found the following that gets the job done with a few additions / considerations:
The majority of the code attached is sourced from this blog entry.
http://blogs.office.com/b/microsoft...namicmenu-to-show-a-list-of-open-objects.aspx
I had a few problems with this. I expected an article like this to include everything needed to get the thing working but alas, it did not. I am posting my solutions in case someone else can find them useful.
1. It clearly states: "Remember that you'll need a reference to the Microsoft Office 12.0 Object Library to compile this code."
What it fails to mention is that this library is loaded by default but the code still will not compile until you add it again. (Offenders include: IRibbonControl, and any other reference to a Ribbon) Yes, this sounds stupid but the reference was checked in my application and the code would not compile until I added a second reference to it. (Tools | References in the VBA window)
2. The code refers to a function / subroutine that is not explained:
BuildAttribute("onAction", "OnOpenObject") & "/>"
Since I have never programmed a dynamic menu before today, I had no idea how to work with it and respond to the user's click. After all, what good is a list of open objects if the user can't do anything with it?
My solution is a bit long winded and includes Object types that probably will never be accessed directly by my users but to provide a complete reference, I dealt with all the objects I could define:
Public Sub OnOpenObject(Control As IRibbonControl)
Dim strType As String
Dim strName As String
strType = Right(Control.tag, Len(Control.tag) - (InStr(Control.tag, "|")))
strName = Left(Control.tag, (InStr(Control.tag, "|")) - 1)
Select Case strType
Case 0: 'Do nothing. The object is a table and should not be open to the user
Case 1: DoCmd.OpenQuery strName
Case 2: DoCmd.OpenForm strName
Case 3: DoCmd.OpenReport strName
Case 4: 'Do nothing. The object is a Macro and should not be open to the user
Case 5: 'Do nothing. The object is a Module and should not be open to the user
'There is no Type 6 to consider See http://msdn.microsoft.com/en-us/library/bb225926(v=office.12).aspx (this is my source for the list of object types)
Case 7: 'Do nothing. The object is a Server View and should not be open to the user
Case 8: DoCmd.OpenDiagram strName
Case 9: 'Do nothing. The object is a Stored Procedure and should not be open to the user
Case 10: 'Do nothing. The object is a Function and should not be open to the user
Case -1: 'Do nothing. The object is a Default Object.
End Select
End Sub
I did try to work through some setfocus commands with no success. If any of you can improve on this, please do.
I exported the module for this function as a text file and added the XML for the ribbon as reference. (RibbonFunctions.txt)
The majority of the code attached is sourced from this blog entry.
http://blogs.office.com/b/microsoft...namicmenu-to-show-a-list-of-open-objects.aspx
I had a few problems with this. I expected an article like this to include everything needed to get the thing working but alas, it did not. I am posting my solutions in case someone else can find them useful.
1. It clearly states: "Remember that you'll need a reference to the Microsoft Office 12.0 Object Library to compile this code."
What it fails to mention is that this library is loaded by default but the code still will not compile until you add it again. (Offenders include: IRibbonControl, and any other reference to a Ribbon) Yes, this sounds stupid but the reference was checked in my application and the code would not compile until I added a second reference to it. (Tools | References in the VBA window)
2. The code refers to a function / subroutine that is not explained:
BuildAttribute("onAction", "OnOpenObject") & "/>"
Since I have never programmed a dynamic menu before today, I had no idea how to work with it and respond to the user's click. After all, what good is a list of open objects if the user can't do anything with it?
My solution is a bit long winded and includes Object types that probably will never be accessed directly by my users but to provide a complete reference, I dealt with all the objects I could define:
Public Sub OnOpenObject(Control As IRibbonControl)
Dim strType As String
Dim strName As String
strType = Right(Control.tag, Len(Control.tag) - (InStr(Control.tag, "|")))
strName = Left(Control.tag, (InStr(Control.tag, "|")) - 1)
Select Case strType
Case 0: 'Do nothing. The object is a table and should not be open to the user
Case 1: DoCmd.OpenQuery strName
Case 2: DoCmd.OpenForm strName
Case 3: DoCmd.OpenReport strName
Case 4: 'Do nothing. The object is a Macro and should not be open to the user
Case 5: 'Do nothing. The object is a Module and should not be open to the user
'There is no Type 6 to consider See http://msdn.microsoft.com/en-us/library/bb225926(v=office.12).aspx (this is my source for the list of object types)
Case 7: 'Do nothing. The object is a Server View and should not be open to the user
Case 8: DoCmd.OpenDiagram strName
Case 9: 'Do nothing. The object is a Stored Procedure and should not be open to the user
Case 10: 'Do nothing. The object is a Function and should not be open to the user
Case -1: 'Do nothing. The object is a Default Object.
End Select
End Sub
I did try to work through some setfocus commands with no success. If any of you can improve on this, please do.
I exported the module for this function as a text file and added the XML for the ribbon as reference. (RibbonFunctions.txt)