Ribbon On The Fly Part2

Bob Phillips — Thu, 03 Mar 2011 12:49:00 GMT

Introduction

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
    
        wsClient.Activate
    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

ws_exit:
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
    Else

        mgInsightRibbon.Close
    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

    Else

        Call OpenRibbonAddin
    End If

Workbook_Open_Tidy:
    PopProcedureStack

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

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

Figure 4 - Test Excel Version

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