Problem in Excel 2002 vs Excel 97

[ed_vak]

This code below works fine in Excel 97 on NT.
After one of our machines was upgraded to XP with Excel 2002 it sto
working with Run-time Error 5 on the second "With..." the first one i
OK:

Sub MakeToolBar()

With Application.CommandBars(1).Controls
.Add Type:=msoControlPopup, Before:=11
.Item(.Count).Caption = "GEF Fi&xes"
.Item(.Count).Tag = "GEF Fixes"
End With

With Application.CommandBars("Custom Popup 1").Controls
.Add Type:=msoControlButton, Before:=1
.Item(.Count).Caption = "Fix &Me"
.Item(.Count).OnAction = "FixMe"
End With
End Sub

Any help or ideas will be greatly appreciated.
Thank you.
E

 

[Jim Cone]

Ed,

The dots are missing in front of "Add" and "Item" in both
portions of the sub. The code won't run without them.

The first portion leaves a blank button on the menu bar and
renames the button to the right of it.

Excel cannot find the "Custom Popup 1" control.
I would have to assume it is missing or was renamed.

Regards,
Jim Cone
San Francisco, USA


message
This code below works fine in Excel 97 on NT.
After one of our machines was upgraded to XP with Excel 2002 it stop
working with Run-time Error 5 on the second "With..." the first one is
OK:

Sub MakeToolBar()

With Application.CommandBars(1).Controls
Add Type:=msoControlPopup, Before:=11
Item(.Count).Caption = "GEF Fi&xes"
Item(.Count).Tag = "GEF Fixes"
End With

With Application.CommandBars("Custom Popup 1").Controls
Add Type:=msoControlButton, Before:=1
Item(.Count).Caption = "Fix &Me"
Item(.Count).OnAction = "FixMe"
End With
End Sub

Any help or ideas will be greatly appreciated.
Thank you.
Ed

 

[ed_vak]

Jim,
Thank you for your reply.

Actually those dots are there.
Hovewer it does looks absolutely right that control is missing (keeping
in mind that machine was upgraded).

What should I check for and where on the other (unupgraded) mashines to
be able to find that control? What options I have? Is it something in
Add-Ins manager?

Thanks a lot. Your help and time are greatly appreciated.
Ed

 

[Jim Cone]

ed,

When you replace one Excel version with another, you lose any/all
of the Menu/Toolbar changes that were made in the older version.
Each application version has it's own set of command bars.
However, any custom command bar "attached" to a workbook created
in XL97 should be available in XL2002 when that workbook is opened.

The list of available Menus and Toolbars (built-in and custom) are
found in View | Toolbars | Customize | Toolbars(tab).

Menus/Toolbars are stored for:
Excel 97 in...C:\WINDOWS\User8.xlb
Excel 2002 in...
C:\Documents and Settings\user\Application Data\Microsoft\Excel\Excel10.xlb

(your mileage may vary)

Your best bet probably would be to start over in XL2002 and
create/build any custom command bars that you need.

Regards,
Jim Cone
San Francisco, USA



message Jim,
Thank you for your reply.
Actually those dots are there.
Hovewer it does looks absolutely right that control is missing (keeping
in mind that machine was upgraded).
What should I check for and where on the other (unupgraded) mashines to
be able to find that control? What options I have? Is it something in
Add-Ins manager?
Thanks a lot. Your help and time are greatly appreciated.
Ed

 

[ed_vak]

Jim,
it looks like you are my life saver!
I am not technical at all - I am on the business side
Hovewer, based on what you have said I should be able to find old *.xlb
that is still sits somwhere on one of our unupgraded machines and copy
it over onto new machine.
I will try it after the long weekend.
If it will not work I will ask someone to create a new custom command
bar.
I will post here what I end up with.
Thank you! and have a great long weekend!
Ed