Posts Tagged 'User Interface'

Navigating Part Relationships – 2

In the previous post, I introduced the idea of a cursor object that allows you to navigate around a graph of component-part relationships:

Navigation could be:

  • down the ‘contains’ relationships
  • along the ‘used in’ relationships
  • back through the history of visited records.

We’ll also have a Reset operation, which jumps back to the start of the table, and clears the history.

The navigation is done using keyboard shortcuts (but could be done via a form).

The core of the design is a Class Module GraphCursor. This provides our four operations: CursorDown, CursorNextUse, CursorBack and CursorReset. When an instance of this class initializes, it points itself at ListObjects(1) on the ActiveSheet (there is only one sheet, to keep things simple), and does a CursorReset.

A GraphCursor maintains a history of visted components using a linked List class (a simple chain of ListItem objects – nothing to do with ListObject a.k.a. Table).

CursorDown and CursorNextUse use Range.Find with the currently selected cell value. I assume this is pretty efficient – and in any case is neater in code terms than iterating through rows explicitly. The Range for CursorDown is just the first column (Component); the Range for CursorNextUse is the Part columns below the row of the current selection.

Something needs to create and hold on to an instance of GraphCursor – this is a standard module Client. This also provides procedures that are called via the keyboard shortcuts.

Public gc As New GraphCursor

Sub GCDown()
End Sub

'similarly for the other three operations

The keyboard shortcuts are set up on Workbook_Open:

Private Sub Workbook_Open()
    Application.OnKey "^+d", "GCDown"
    Application.OnKey "^+n", "GCNextUse"
    Application.OnKey "^+r", "GCReset"
    Application.OnKey "^+b", "GCBack"

End Sub

Here’s the workbook (Excel 2007).

Since each navigation step is worked out dynamically, we can insert or delete records from our table as we like. This would not be the case for an indexed solution (maintaining a map of Component to row number).

You could argue that each Component-Part relationship should be a separate record – for example, [A, B], [A, C]. This would allow us to associate quantities or other information with each relationship. In this case, we would also need a CursorNextPart operation.

Navigating Part Relationships

This is something I was thinking about before a short holiday break (Strasbourg – very pretty) …

Suppose that we have an inventory of Components, perhaps in a manufacturing or engineering context. For each Component, we record its Parts (that is, sub-components). For example:

Sorry, my creativity did not extend to dreaming up meaningful data – think of car parts, or something.

So, an A is a top-level component, and consists of Bs and Cs (we do not specify quantities – an issue deferred); a B consists of Fs and Gs; E, G and H are elementary components (indicated by underscore). A component, such as B or H, can be used by multiple higher-level components. The restriction to 3 part types per component is just for simplicity.

These relationships form a directed, acyclic graph (there are top-level and bottom-level components, and no component can contain itself directly or indirectly).

Even with a very simple example, like above, it is quite difficult to see what contains what. With a realistically large inventory, say hundreds or thousands of records, it would be next to impossible.

One solution would be to support navigation around the graph of relationships:

  • Drill-down the ‘contains’ relationship: for example, [A contains B, C] >>> [B contains F, G] >>> [F contains H] >>> [H is elementary]
  • Move along the ‘used in’ relationship: for example, [B used in A] >>> [B used in D] >>> [no more uses of B]

Moving along the ‘used in’ relationship for ‘_’ (underscore) takes us through all the elementary components.

When you get the end of a ‘used in’ chain, you could have the option to start again from the beginning (as in textual searches):

In addition, we could have navigation ‘back’ through the sequence of visited Components.

We could offer these operations and the resulting ‘current record’ to a user via a form. However, it seems more lightweight to use keyboard shortcuts.

So, how might we do this? As previous postings have illustrated, I like the idea of a cursor object, which provides move operations of some kind, and access to the ‘current record’. In this case, the cursor position will be indicated visibly to the user by explicitly Selecting the relevant cell (and thus scrolling to the record).

An instance of the cursor object can be created on Workbook_Open, and linked on Class_Initialize to a the table. For simplicity, let’s assume that we have a single worksheet with a single table. As usual, there are advantages to using a v2007 Table (in VBA a ListObject), but you could get it to work in v2003.

More on the design/code in the next posting.

Dynamic Combo Box List

A combo box on a form can pick up the list contents from its RowSource property. This can be the name of a worksheet range – such as “products” (not ever a literal address, please).

However, this is tricky if the size of the list changes (if the list grows, specifically). With a standard defined Name, you need to keep on redefining the Name. Alternatively, you can use the OFFSET-function trick for dynamic ranges (but that is obscure and depends on things like the number of non-data cells in the column).

In 2007 we have the Structured Reference naming into Tables, such as “ProductTable[Product]”, which returns us the data contents of the product column. On a recent course, I was asked if we could use this as a Combo box RowSource property. The good news is that we can:

Any additions to the Table are picked up when the form reopens:

You could get this to work for a multi-column list (e.g. id + name, with the id as BoundColumn). For a 2-column table, you can specify the whole data area: “ProductTable[#data]”. It doesn’t matter if the #data area has more columns than the list needs: the leftmost ones are used.

Initially, I thought I’d try this with an ActiveX combo box dropped directly on to the worksheet. However, such on-sheet controls are actually OLEObjects. Rather than a RowSource property, they have a ListFillRange. An OLEObject could be things other than a List/Combo box, in which case this property would be inapplicable. (Similarly, RowSource is actually defined on the Control class).

That’s okay, but unfortunately, OLEObject.ListFillRange does not understand structured references into tables: it needs either a regular Defined Name or (don’t) a literal address. This seems like an oversight: the OLEObject‘s Parent is the Worksheet, which holds the Table as a ListObject – so it should be possible to resolve the name.

Nevertheless, it works fine for the Form combo boxes, and I’ll try to use this technique in the future.

RibbonX Visual Designer

Over at Daily Dose of Excel, Juan Pablo Gonzalez posted (in a comment) a link to Andy Pope’s site, where he has a very slick looking tool for Ribbon extensions, the RibbonX Visual Designer. I plan to download it and give it a go: more anon.

Happy New Year, and thanks for visiting.

Office 2007 Ribbon – Part 3

In the previous part we started to implement a custom tab in the Excel ribbon:

We defined an XML configuration file for the tab and the Link group, which lives inside the xlsm archive. We also wrote VBA callback procedures for the buttons’ onAction events.

For the next stage, we want to be able to create, go to and delete ‘bookmarks’ within our workbook. Bookmarks are range names (maybe temporary) which are distinct from the range names used in formulas. We’ll make them distinct using a suitable prefix – say “_BM_”. Our combo box will display only these names (minus prefix), not the full set visible in the Name Box combo.

We can define a bookmark by selecting the target range, and clicking the Make Bookmark button. An input box has a default value of the first (or only) cell; this can be changed, but the name must not be already in use. The combo box then needs to be updated with the new name (minus prefix).

Selecting from the combo box goes to the bookmark range. We need to update the combo to clear the selected value (otherwise selecting the same value again does not raise an onChange event). The Delete Bookmark button does what it says; again we need to update the combo.

First the configuration. At the same level as the Links <group> element, we need to add:

<group id="BookmarkGroup" label="Bookmarks">
  <button id="BookmarkButton"
	label="Make Bookmark"
	onAction="Bookmark.MakeBookmark" />
  <comboBox id="BookmarksCombo"
  <button id="BookmarkDelButton"
	label="Delete Bookmark"
	onAction="Bookmark.DeleteBookmark" />

The buttons are as the previous examples. The combo box is rather more interesting, in that it is built by means of callbacks: GetItemCount, GetItemLabel, GetItemID, GetText. We need to provide VBA implementations for these:

Sub GetItemCount(control As IRibbonControl, ByRef count)
    count = UBound(bmarks)
End Sub

Sub GetItemLabel(control As IRibbonControl, index As Integer, _
           ByRef label)
    label = bmarks(index + 1)
End Sub

Sub GetItemID(control As IRibbonControl, index As Integer, _
           ByRef ID)
    ID = "bookmark" & index
End Sub

Sub GetText(control As IRibbonControl, ByRef text)
    text = ""   'visible value in combo when initialized
End Sub

The Item Labels are the visible items in the combo box drop-down. In our case, these come from an array of the Bookmark names bmarks, which is held as a module variable. The bmarks variable is set by a procedure which goes through the ActiveWorkbook Names, extracting the Bookmark ones.

The Label and ID procedures take an index parameter, which implies that they are called iteratively by the ribbon. Notice that these are subs (rather than functions), which pass back their result via a ByRef parameter.

Now we need to make the combo list update whenever an action has been performed. We do this by making a call to the ribbon (I suppose this is a ‘callforward’):

Sub ResetBookmarks()
    Bookmarks   'reset bmarks array
    ribbon.InvalidateControl ("BookmarksCombo")
End Sub

The argument is the control Id, as in the configuration. We can also invalidate the whole ribbon, should multiple controls need re-initializing.

How do we get the ribbon reference? Via a callback when the ribbon loads initially. The callback is specified on the root node of the configuration:


The RibbonLoad procedure gets a ribbon reference, assigns it to a module variable, and also sets the Bookmarks array:

Sub RibbonLoad(rb As IRibbonUI)
    Set ribbon = rb
End Sub

That’s about it. As I said earlier, the documentation – for example of the callbacks – isn’t great, so a certain amount of experimentation is needed.

Here’s the XML configuration file for the ribbon extensions; here’s the Definition module; and here’s the Bookmark module.

Office 2007 Ribbon – Part 2

Carrying on from the previous post

The first thing is to think of some example facilities that will be useful independently of a particular workbook structure. Some ‘smart’ navigation, perhaps:

  • Pick up the value of the currently selected cell, treat it as a range name, and go to that range. This could be useful where we have lots of data values that reference people, locations, parts, etc, and elsewhere in the workbook we have definitions or further information about those entities, with the definitions being named accordingly. Having looked up a definition, we then want to go back to the original reference.
  • Create, go to and delete ‘bookmarks’ within our workbook. Bookmarks could well be temporary, and should be distinct from the standard range names.
  • In both cases, we’d like some user interface other than the Name Box drop-down.

Here’s what it might look like:

This is a custom tab (called Navigation), with four buttons and a combo box. The extensions could have been added to a built-in tab, instead.

The key difference from earlier versions of Excel (Word, etc) is that the configuration for the ribbon extensions is specified in an XML file that lives within the document ‘file’ (.xlsm, in our case). This is really a zipped archive, which you can open; when I refer to ‘archive’, this is what I mean. The XML file is customUI\customUI.xml (create the folder initially). Here’s the configuration with the Definition controls:

<?xml version="1.0" encoding="UTF-8"?>
      <tab id="NavTab" label="Navigation">
        <group id="LinkGroup" label="Link">
          <button id="DefinitionButton"
            onAction="Definition.GoToName" />
          <button id="BackButton"
            onAction="Definition.GoBack" />

The hierarchical structure of ribbon, tabs, groups, controls is fairly clear.

The button images are standard MS ones, of which there are many. Ron de Bruin has pointers to information on these (see his Section 5).

Note the onAction attributes, which are callbacks to procedures in our VBA code, of the form Module.Procedure.

Names of attributes and built-in entities are case-sensitive, unlike VBA code – beware! Errors in the XML configuration result in the extensions not loading, with no error messages or other indication (unless I’m missing something).

In order to get the customUI extensions picked up by the application, you need to add a declaration in the archive file _rels\.rels:

<Relationship Id="customUIRelID"

Once added, this does not need to change.

Incidentally, I’m putting this in an xlsm document, but it would in practice go in an xlam (add-in).

We now need to put the callback procedures somewhere in our VBA project. The module doesn’t matter, but must be the one named in the ribbon configuration: Definition.

Dim source As Range 'module level

Sub GoToName(ctrl As IRibbonControl)

 On Error Resume Next 'ignore if name not defined

 Dim val As String
 Dim name As String

 Set source = Selection
 val = source.Value
 name = ActiveWorkbook.Names(val).name
 Application.Goto Reference:=name

End Sub

Sub GoBack(ctrl As IRibbonControl)
 On Error Resume Next
 Application.Goto Reference:=source
End Sub

Note the standard parameter to control callbacks, of type IRibbonControl. From this reference you can get the control Id (as in the configuration). Casing on this would allow you to use the same callback procedure for multiple controls. You can also get the Context, which in this case at least is the Window.

The alert reader (I love saying that) might have spotted a callback in the root <customUI> element in the configuration:


This allows our VBA to get and hold on to a reference to the ribbon itself. We’ll need this in order to change the contents of our combo-box dynamically (see next installment).

Now, if this all seems a bit of a performance, you might have a point. If you want a bit of help with the XML editing, and updating the document archive, there is a simple helper application, the Custom UI Editor. Personally, I’d rather use a proper XML editor, and just drag-and-drop files into the archive. Note that you can’t have the document open as both an archive and an Excel workbook at the same time – unsurprisingly.

I’ve found the Microsoft documentation on this a bit patchy – I haven’t found a proper reference manual. The main source seems to be a set of three articles on MSDN – search for “Customizing the 2007 Office Fluent Ribbon”.

In the next post we’ll add the Bookmarks controls, with a combo-box that we update dynamically.

Office 2007 Ribbon

I’ve not previously done much with CommandBars in 2000/2003, since my applications were tied to particular workbook structures, and thus used forms or on-worksheet controls. However, I think it’s time to have a look at the Ribbon extensibility in Office 2007.

Although the Ribbon has been criticized for taking up a lot of screen area, its size means that there is more scope for displaying useful information, rather than just holding toolbar buttons. So it might be possible to use a custom ribbon tab as a permanently visible form, with combo boxes and other controls. Obviously, any facilities still have to be applicable to a wide range of differently structured workbooks. I’m thinking of various ‘smart’ navigation facilities, as an example.

A significant difference with the 2007 ribbon is that the customizations are expressed in XML, and added to the workbook ‘file’. As is well known, an Office 2007 file is actually a zipped archive containing various folders and files. You can open it with WinZip (or 7-Zip, if you prefer a free tool).

It’s apparent from the Microsoft technical articles that the approach is not specific to VBA, but is also intended for heavier-weight usage by C#/.Net developers – hence the use of XML configuration. However, this means that while it’s quite easy to stick a few command buttons on a ribbon tab, things can get quite a lot more complicated… I’m still investigating, and will post again in a day or so.

November 2019
« Dec