Ribbon On The Fly Part2

Microsoft sneaked a couple of ne


In my last blog post, I described how I thought that I could build an Excel 2007 ribbon on th fly. The technique was founded upon having a ‘worker’ addin that handled the main functionality as well as the version management and ribbon building; and a simpler Excel 2007 ribbon wrapper addin.

This approach would be similar to the myriad of table driven menu solutions around, but the ‘table’ would be a configuration file in this case, to allow the user to control the menu/ribbon.

The ‘worker’ addin would read the configuration file that held the details of the each procedure to be run, and construct the menu or ribbon on the fly. In the case of the ribbon, the customUI XML would be generated, written back to the (as yet) unopened ribbon wrapper addin, then open the ribbon wrapper addin so as to display the changed ribbon in all its pristine glory.

At that point, this was just an idea, albeit an idea that I was confident that it could be implemented. Since then I have implemented it, so it is time to share the details.

Linking the Ribbon Addin to the ‘Worker’ Addin

Having a ‘worker’ addin and a separate ribbon presentation addin does create one problem, namely how dos a button on the ribbon run a procedure in the ‘worker’ addin. This is easily resolved using Application.Run, rather than a simple call to the procedure, as this allows specifying the file as well as the procedure name.

In fact, this allows even greater flexibility when deploying in the ‘real’ world. If we release our super application, with a configuration file, the ‘worker’ addin, and a ribbon addin, a user can add items to the configuration file as long as it runs a procedure already defined in our ‘worker’ addin. But what if they want to run a completely new process, how do we provide the ability to extend the applications overall functionality? We could just open up the addin and tell the user to do add thewir code to the ‘worker’ addin, but is this a good idea? I don’t think this is good, it could break the whole application. A better way is to tell the user that they can build their code in an entirely separate project, and addin, Person.als/xlsm, or whatever they wish, and the include the full file/procedure call in the configuration file procedure column.

Format of Configuration File

It is probably a good point to describe the configuration file at this point.

I have set it up as a standard Excel workbook, in Excel 2003 format for backwards compatibility, and I have a separate tab for each (user) functional ribbon group (or sub-menu for Excel 2003).

I built this technique to service the running of a set of user reports, which are all template files and generally all run the same process, but it could be anything that you want, you would just setup the configuration data, and extract it in the configuration management code.
Figure 1 – Configuration File Group shows a typical layout. If you have any other attributes that you need to capture, just insert extra columns in the first part (in my application, I was importing XML files, so I had to specify the XML table worksheet etc.).

As you can see, some of the columns are relating to the report running, and some relate to the presentation of the ribbon.

Figure 1 – Configuration File Group

I have a column for Report Type, this is so that the client can have the templates in different locations, say company reports on a central server, departmental reports on a departmental server, and the user can have their own report templates locally.

This is all defined on the Client worksheet, Figure 2 – Configuration File Client.

Figure 2 – Configuration File Client

This simply allows the user to provide the client name to appear on the ribbon/menu, a logo file, and the various template directories. Again, if you have a requirement for other client related details, add them here.

The only amendable field here is the client name, the directory values are added by clicking the browse buttons.

I have also added a button to add new groups sheets, so as to keep a consistent format.

Check the Configuration File

Writing the XML back to the ribbon addin is an expensive process, cracking open the zip file, updating the customUI.xml file and then re-writing the file, so I have added a check to ensure this is only done if and when the configuration file is updated.

This is simply achieved with some simple change event code in the configuration file, Figure 3 – Configuration File Change Code, lines 440-450 simply setup a defined name with a value of TRUE. This is checked in the ‘worker’; addin initialise code to determine whether to update the ribbon addin or not.

As you can see, there is a lot of code here for creating a simple name. In addition to this, the code also checks for a duplicate report ID, resetting if a duplicate value is added (this is to avoid compromising the ribbon).

Private mcPrevValue As Variant

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = WS_TEMPLATE Then

End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim mpName As Name
Dim mpSheet As Worksheet
Dim mpDuplicateRepID As Boolean
Dim mpDuplicateGroupID As Boolean
Dim mpLastrow As Long
Dim i As Long

10 On Error GoTo ws_exit

20 Application.EnableEvents = False

30 If Sh.Name = "Client" Then

40 ElseIf Sh.Name = "_template" Then

50 Else

60 If Target.Column = 1 Then

70 mpDuplicateRepID = False
80 For Each mpSheet In Sh.Parent.Worksheets

90 If mpSheet.Name <> WS_CLIENT And mpSheet.Name <> WS_TEMPLATE Then

100 mpLastrow = mpSheet.Cells(mpSheet.Rows.Count, "A").End(xlUp).Row
110 If mpLastrow > 2 Then

120 For i = 3 To mpLastrow

130 If Sh.Name <> mpSheet.Name Or Target.Row <> i Then

140 If mpSheet.Cells(i, "A").Value2 = Target.Value Then

150 mpDuplicateRepID = True
160 Exit For
170 End If
180 End If
190 Next i

200 If mpDuplicateRepID Then Exit For
210 End If
220 End If
230 Next mpSheet

240 If mpDuplicateRepID Then
250 ShowMessage Replace(MSG_ERROR_DUPLICATE_REPORT, _
"", Target.Value), vbOKOnly + vbExclamation
260 Target.Value = mcPrevValue
270 End If
280 ElseIf Not Intersect(Sh.Range(NAME_GROUP_ID), Target) Is Nothing Then

290 mpDuplicateGroupID = False
300 For Each mpSheet In Sh.Parent.Worksheets

310 If Sh.Name <> WS_CLIENT And Sh.Name <> WS_TEMPLATE And Sh.Name <> mpSheet.Name Then

320 If mpSheet.Range(NAME_GROUP_ID).Value2 = Target.Value Then

330 mpDuplicateGroupID = True
340 Exit For
350 End If
360 End If
370 Next mpSheet

380 If mpDuplicateGroupID Then
390 ShowMessage Replace(MSG_ERROR_DUPLICATE_GROUP, _
"", Target.Value), vbOKOnly + vbExclamation
400 Target.Value = mcPrevValue
410 End If
420 End If
430 End If

440 Set mpName = ThisWorkbook.Names.Add(Name:="_Changed", RefersTo:="=TRUE")
450 mpName.Visible = False

460 mcPrevValue = Target.Value
470 Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
mcPrevValue = Target.Value
End Sub

Figure 3 – Configuration File Change Code

Testing Excel Version

Determining whether to build a menu or a ribbon is very straight-forward, all we need to do is to check the application version, as shown in Figure 4 – Test Excel Version. There is nothing particularly trick here, but there are a few call to other procedures that will be covered in the next post, namely

  • AppInitialise – opens the configuration file, does some basic validation of the template paths, and then grabs all of the report details
  • OpenRibbonAddin – this checks if the configuration file has been changed since the last run, builds the XML, and writes it back to the ribbon addin.

There is one other procedure call, BuildMenus, which I will not go into, it is a standard commandbar menu builder.

Private Const mmModule As String = "ThisWorkbook"	

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Val(Application.Version) < 12 Then

Call DeleteMenus

End If
End Sub

Private Sub Workbook_Open()
Const mpProcedure As String = "Workbook_Open"

On Error GoTo Workbook_Open_Error
PushProcedureStack mpProcedure, True

Call AppInitialise
If Val(Application.Version) < 12 Then

Call BuildMenus
mgConfigWB.Close SaveChanges:=False
Set mgConfigWB = Nothing


Call OpenRibbonAddin
End If


Application.DisplayAlerts = True
If Not mgConfigWB Is Nothing Then mgConfigWB.Close SaveChanges:=False
Set mgConfigWB = Nothing
Exit Sub

If Err.Number = AppBypassErrorNum Then Resume Workbook_Open_Tidy
If AppErrorHandler(mmModule, mpProcedure, True) Then
Resume Workbook_Open_Exit
End If
End Sub

Figure 4 – Test Excel Version

Next Post

In the next post, I will give details on the getting the configuration file details, building the ribbon XML, and using the addin.

One thought on “Ribbon On The Fly Part2

Leave a Reply

Your email address will not be published. Required fields are marked *