Custom tooll bars in excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hughg - 27 Nov 2007 10:55 GMT

I have made custom tool bars with menus and macros attached in excel
2003
Having upgraded to Excel 2007, I can see the custom tool bars in the
addins tab but when I try to use them the path isn't recognised.

When I right click on a menu there's no option to assign a macro, just
an option to customise to the quick access toolbar. This doesn't give
the capability to attach macros to customised tool bars.

Please can somebody suggest a workround?

Many thanks for looking

HVG

Reply to this Message

Jim Rech - 27 Nov 2007 13:31 GMT

Cute that MS took away the UI for assigning macros to custom toolbuttons and
menu items now appearing on the Add-ins tab. The old toolbars are still
there though so you can find their controls and change their OnActions with
code. For example:

CommandBars("MyToolbar").Controls(5).OnAction = "Personal.xls!MyMacro"

When you run code like this and exit Excel the changes seem to be saved in
your Excel12.xlb so they should be retained.

I just thought of another way that may be easier. If you can move the
add-ins/Personal, etc. back to the path where they used to be (maybe you
have to temporarily recreate the paths), when you start Excel 2007 the
toolbuttons/ menu items should find their macros and run them. If so, you
could then do a Save As of the add-ins, etc. and Excel should adjust to the
new path. With add-ins you could either make them non-addins using the
IsAddin property for this exercise or save them through code.

Signature

Jim

|I have made custom tool bars with menus and macros attached in excel
| 2003
[quoted text clipped - 10 lines]
|
| HVG

Reply to this Message