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.