Creating a toolbar that works in Excel 2000 and 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bonsai Bill - 20 Sep 2007 03:50 GMT

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.

Reply to this Message

Jon Peltier - 20 Sep 2007 04:35 GMT

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> 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
[quoted text clipped - 19 lines]
> Thanks for any help on this! I am somewhat lost in finding compatible
> code.

Reply to this Message

Bonsai Bill - 21 Sep 2007 03:28 GMT

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.

> 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
[quoted text clipped - 38 lines]
> > Thanks for any help on this! I am somewhat lost in finding compatible
> > code.

Reply to this Message

Jon Peltier - 22 Sep 2007 03:01 GMT

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> 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
[quoted text clipped - 52 lines]
>> > Thanks for any help on this! I am somewhat lost in finding compatible
>> > code.

Reply to this Message