11 thoughts on “Customizing The Excel 2007 UI”

1. Wow, somebody really WAS listening! It wasn’t at all obvious. This might rate the UI as merely hard to use, not impossible.

   – Jon

2. Yes, RibbonX is actually pretty good for static pre-definable menu structures. Most of us use some sort of table-driven menu creation for our commandbars; RibbonX is just replacing that with an xml stream instead of an on-sheet table and including standard code to apply that XML. Life isn’t quite so rosy when we want to make our UIs more dynamic. We can hide and show pre-defined controls, or use dynamic menus and galleries for some flexibility, but we can’t add, delete or redorder controls during the life of our applications. To be fair, the dynamic menus and galleries gives us as much as we had with previous versions of Office, but we can’t use any of the new controls (tick boxes, etc) in a dynamic manner. Overall, I’d class it as sufficient, but a missed opportunity to be really good.

3. This really highlights the need to learn about XML and Microsoft DOM objects. I know that XML has always seemed like a solution to a problem which hasn’t happened yet. But Office 2007 changes all that. With the new file formats, you can get into the guts of one or many documents and do some really cool things by traversing the XML within.

4. BTW,

   Microsoft’s Savraj Dhanjal talks about Customizing the UI and RibbonX in this video presentation.

   http://microsoft.sitestream.com/PDC05/OFF/OFF302_files/Default.htm#nopreload=1&autostart=1

5. Thanks John! It look promising but I believe Stephen raise some important questions about it in the referred first link. In addition, will it work with different language versons as well? Although XML has been around for the last 5-6 years it will challenge the larger group of Excel developers and power users which the publishers/writers hopefully will cover in upcoming Excel books.

   Kind regards,
   Dennis

6. Stephen:
   on the fly creation is possible but it looks like you need a ComAddin to make it work.
   I tried posting the code here, but somehow my post failed so I’ve mailed you an example.

   VBA is used to manipulate XML customization string and execute the calls. The XML string and Procedure names are (temporarily) stored in the registry, then the ComAddin is connected.
   On connection the commaddin creates the controls from the XML. While loaded it passes calls back to the specified VBA procedures.

   Jurgen

7. Jurgen, I’ve played around with that a little and it only works in very specific situations. For example, as the names of all the callback procedures have to be predefined, we would typically only be able to have one procedure for each type of control callback (whereas the example typically has a specific procedure for each callback of each control). The only way I could envisage working in the general case is for us to have a marshalling COM Addin, to which we provided the xml definition file and a class instance. That addin would replace all the callback procedure names with a single, standard name, but keep a lookup list to identify which control ID was linked to which procedure in which class and thereby know which one to call. I requested that Microsoft implemented such a design themselves when I saw the first dogfood builds, but they decided not to. The other major complication is that COM Addins only get one opportunity to provide the custom UI xml, when they’re loaded. So if we want to have the UI changing during the application’s lifetime (e.g. adding extra controls/chunks as new sheets get added to a workbook), the only way to do it is to unload and reload the COM Addin – which would have to work properly when potentially multiple client workbooks are using it.

8. RibbonExtensibility is meant for Design Time, much like forms.. All controls to be created on startup. Visibility/Labeling etc can be manipulated thru callbacks. The only control which supports runtime manipulation is the DynamicMenu via getContent.

   VSTO will have a designer. Simple VBA hacks like me will have to settle for notepad/winzip.
   until someone comes up with a VBA addin to design Ribbons and generate the XML.

   loading sequence: http://blogs.msdn.com/andreww/archive/2006/04/04/568607.aspx
   schema details: http://officeblogs.net/UI/customUI.xsd

   BTW:
   I’ve found some examples of manipulating ‘OpenXML Package’ with .NET Anyone got ideas on how to do this with VB(A) ?

9. Hi Dick,

   I signed up too. Let me know if you get accepted!

10. >I’ve found some examples of manipulating ‘OpenXML Package’ with .NET Anyone got ideas on how to do this with VB(A) ?

    All indications is that the Packaging API will not be available via VBA. This leaves Excel developers with the nasty prospect of a four step process.

    2: Create a temp file
    2: Extract contents into the temp file using a third party library
    3: Manipulate Open XML
    4: Zip the contents (again, using a third party library)

11. I’m working on a “table driven” Ribbon customization.

    For starters I’d like to focus on minimizing XML editing and maximizing flexibility by reading the inputs from excel tables. Maybe the ‘creation during lifetime’ will find a solution, and hopefully MS will surprise us and extend programmatic access to the ribbon.

    I’m using the delegate callbacks where possible, while keeping the number of callback procedures to the bare minimum. Each callback uses the control’s ID and select case or lookups to do its work.

    I’d like to have one or two ‘development buddies’. Skype, Curiosity and Creativity wanted :)
    Anyone interested? Drop me a mail.

    keepITcool (AT chello DOT nl)

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.