ExcelBanter - Macros fail if file saved in another location

Hi PaulM,

I have created a new application which incorporates a custom menu bar
with a range of options that are driven by macros specified in the
"Assign Macro" section of the option definition.

Works great until you move the file to another location and then try to
load it up. Excel gives an error that there is already a copy of this
file running and it cant load a second copy, so the macros are
obviously looking to load up the original file to be able to access the
macros as originally defined.

Anyone had this problem before and if so do you have a fix?

Excel keeps toolbar and menubar customizations in a file with the
extension .xlb. The exact filename depends on Excel version and install,
but usually is: Excel9.xlb or Excel.xlb or Username8.xlb.
Often this file can be found in your WINDOWS directory.

You can attach a toolbar to a workbook. When this workbook is loaded, XL
checks if the toolbar is on the system. If not, it copies the toolbar
from the workbook to the system.

After creating *or changing* the toolbar, you should attach the toolbar
to your workbook:

- activate the workbook to which you want to attach the toolbar
- Rightclick the toolbar, select 'customize'
- Click 'Attach' (Toolbars Tab)
- If the workbook already contains a toolbar by that name, delete it
first by clicking on it on the righthand side and choosing Delete.
- Select your toolbar (on the left) and press 'copy'
- Save the workbook (optionally: save_as an add-in).

Also, you should include code that deletes the toolbar when your workbook
or add-in is closed, so that when you deliver a new version of your
workbook the new toolbar will be used i.s.o the old one. You can do that
in the Thisworkbook module, using the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
On Error Resume Next 'In case Toolbar is absent
Application.CommandBars("YourBarsName").Delete
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com