Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Custom menu changes path with macros on SaveAs (Excel xp)

    I have a workbook that has macros in it called Main.xls. These macros are attached to a toolbar i call "s-tools".

    I do a "SaveAs" to a new workbook called Spec.xls. The macros and toolbar are in the new excel workbook called spec.xls. That works fine.

    My problem is that when i go back to Main.xls, the toolbars look for the macros in spec.xls. It automatically updates my macro paths in the toolbar of the original file. Is there a way for me to prevent that from happening? Thank you.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom menu changes path with macros on SaveAs (Excel xp)

    No way to prevent it from happening. The solution is to include code in the workbook, that deletes the toolbar from your system when it is closed. Loading the other file will then force Excel to load it's attached toolbar.

    Here is my boilerplate explanation on custom toolbars:

    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()
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •