Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macros and Toolbars (XL2003 SP1)

    We have a tracking spreadsheet that changes several times per day. An audit of these changes is required, so we
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

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

    Re: Macros and Toolbars (XL2003 SP1)

    You have two options.

    1. Use the workbook_beforeclose event to delete the toobar from the system (since it is attached to the workbooks, it will reload when a workbook is opened).
    2. Move your code out of the workbook, into an add-in.

    I'd go for option #2!

    Read my pages about distributing macros for more information:

    http://www.jkp-ads.com/Articles/DistributeMacro00.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Macros and Toolbars (XL2003 SP1)

    Jan Karel,

    Thanks for your response. I'd intended to include your first suggestion when I was reading up on how to implement the effect I wanted, but forgot it while refining the toolbars and macros themselves. I prefer this option because it's integral to the workbook and doesn't depend on third parties receiving and including Add-Ins.

    I've tested it with the code included and everything works as I want. I find it cumbersome and somewhat counter-intuitive to remove and re-attach the toolbar if you amend it or the code behind it, but once it's done, it's done. I just hope I remember this little routine when someone asks for an amendment a few months down the line!

    Many thanks, again.
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macros and Toolbars (XL2003 SP1)

    > I find it cumbersome and somewhat counter-intuitive to remove and re-attach the toolbar if you amend it

    This has to do with the way Excel stores toolbar customizations.

    Word stores them in templates, so that each template can have its own customizations. when you make a template available to other users, they automatically get the toolbar customizations.

    Excel stores them in a file with extension .xlb. This file is stored in your personal profile, so the customizations will not automatically be available to other users. You can do that by attaching the toolbar to the workbbok. However, when you edit the custom toolbar, you are in fact editing the one in your .xlb file, not the one attached to the workbook, so other users won't see the changes. That's why you have to delete the toolbar from the workbook and reattach it, aand delete the toolbar when the workbook is closed, so that it will be reloaded from the workbook next time it is opened.

Posting Permissions

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