PDA

View Full Version : Sleeper: Help with Renaming Excel Files & Toolbars..



Michael 514
03-07-2005, 09:32 AM
Hi there!

How is that for an archaic title to this thread?

I did a cursory search here, but cannot locate an answer to what is probably a simple question.

I have created some macros in Excel (I confess -- keystroke macros, not VBA coding...).

Anyway, I dragged them up to the button bar.

Everything works fine, until I rename the Excel file.

Correct me if I am wrong, but it appears as though Excel adds the filename before the macro name in the Toolbar.

Therefore, the minute I rename my document, the macro cannot be found anymore.

Does anyone know what I am talking about?
Is there a simple work-around?

Ideas are welcome!!

Thanks!

Mike

Richie(UK)
03-07-2005, 01:12 PM
Hi Mike,

Unfortunately this is a common problem with custom menus that are created 'by hand'. The best fix is to have the menu created with VBA.

There are plenty of threads dealing with the creation of menus with code (just try a quickj Search). However, if you need a hand - just shout ;)

MWE
03-08-2005, 04:56 PM
The problem with adding a menu item (and link to macro) via Customizing the Excel menu bar is that the new menu item is associated with Excel as an application not with the particular file.

As indicated above, a better approach is to create the menu on-the-fly with VBA.

However, there is a method that uses standard Excel menu customization and is independent of the particular file. Thus if you change the file name, the menu still works. If you place the macro(s) in your personal.xls file (which is opened automatically [but hidden] whenever Excel starts up) and reference them from the Excel menu, they will work for any Excel file. Unfortunately this also means that they will work on files where they make no sense.