Creating a toolbar that works in Excel 2000 and 2007

[Bonsai Bill]

Could someone please point me to sample code that generates a toolbar that
will work in both 2000 and 2007? Until 2007 the following code worked well:
With Application.CommandBars
With .Add(mcstrToolbarName, , False, True)
.Visible = True
.Position = msoBarBottom
With .Controls
With .Add(msoControlButton)
.Caption = " Inventory exam items and build exam "
.FaceId = 592
.OnAction = "MainProgram"
......................
......................
End With
End with
End with

Is there another option that would be clean and more compatable? If so, I
would appreciate being pointed to some sample code.

Thanks for any help on this! I am somewhat lost in finding compatible code.

 

[Jon Peltier]

All the nice commandbar code developed for Excel 97-2003 doesn't work in
Excel 2007. For pseudo-compatibility, if the commandbars and menus you
created in past versions wasn't too complex, you get the various buttons
appearing on a ribbon tab called "Add-Ins". It's not as flexible and
convenient as in prior versions, and I've had lots of code that worked just
fine in 2000-2003 just fall down in 2007.

There are ways to develop custom ribbons in 2007, and some utilities are
starting to become available. One nice one was constructed by Patrick Schmid
(Google his name or "ribboncustomizer"). The XML code for this lives in the
new Office file format.

- Jon

 

[Bonsai Bill]

Thanks Jon for your reply. I feel like I have been cut off at the knees
regarding having compatible code. I am going to have to rethink how I present
macro options. I would be interested if you or others have found satisfactory
alternatives.

Thanks for your help.

 

[Jon Peltier]

I have only one client using Excel 2007 at this point, and he's barely using
it yet. I had two others, but one switched back right away, and the other is
getting ready to. The problem is that Excel 2007 has broken and bent
behaviors that in the past were well understood, and so far there aren't
even workarounds for many of the intentional and inadvertent changes. (For
example, charts behave differently, code behaves differently, the whole user
interaction is completely twisted.) Changes are minor, but still mean you
have to tinker under the hood of a great many existing, working solutions.

Basically, I don't have a strategy yet. There are enough differences between
UIs in 2003 and 2007 that I would consider supporting two versions of a
utility, so that changes to adapt of one versions UI wouldn't hose the
other's.

- Jon