Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Toolbars (Excel XP)

    I just stumbled onto something that I think might be a problem in the template I'm working on. This is what appears to be happening.

    I have a custom toolbar which I delete when the workbook closes.

    If I have an open workbook which is based on this template, and I open another workbook, also based on this template, to check something, for example, and then I close the second workbook, it deletes my toolbar. So when I return to the first workbook, I no longer have the custom toolbar (at least, until I close and open the workbook again). Is this an accurate description of what I seem to see happening? And is there a work-around so that my users won't lose their toolbars in the middle of working on a document? I can envision it to be a common practice to open an existing workbook while working on a new one. Thanks!

    --Karyl

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Toolbars (Excel XP)

    If you use the Window Activate event to build/display your toolbar, and the Window Deactivate event to Hide your toolbar, that should not be a problem.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Toolbars (Excel XP)

    Doesn't that still leave the toolbar hanging around, so that revised toolbars in future editions of the template don't ever show up on the user's systems? Or am I missing something?

    --Karyl

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Toolbars (Excel XP)

    I'm not sure what you mean. If you display the tool bar in the Window Activate routine, you can display any tool bar your code wants to. When the last active instance of Excel is closed, the tool bar will be destroyed. You can use the Open Event routine to build the toolbar, or you can attach the toolbar to the workbook. If you attach the tool bar to the workbook, then whatever tool bar is attached is the one that will be loaded when the workbook is opened.
    Legare Coleman

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

    Re: Deleting Toolbars (Excel XP)

    So in fact you have a template with a toolbar and code that you distribute. Each copy based on that template will have the code and toolbar too.

    To ensure the toolbar points to the macros in the active workbook, you will have to add code that:
    - removes the current toolbar on de-activation of the workbook that was open before opening a new one
    - builds the toolbar so it points to the now active workbook's macros.

    It would be much easier to create a separate file with your macros and toolbar that can be installed as an addin. You need to check the code to see that it points to the active workbook where appropriate and maybe add a check whether the active workbook is one based on the template (so the macro does its job on the right data <img src=/S/smile.gif border=0 alt=smile width=15 height=15>). But if you ever change your code, you only need to update the addin, not all templates and files based on them.....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Toolbars (Excel XP)

    I don't have time to investigate the add-in option before my demo on Tuesday, but it sounds like the way I need to go with this before it is released company-wide. I've never worked with add-ins, but I'm sure there is a lot of reference material available. So I'll be back when I've had time to read up on it and then get stuck trying to implement it! Again, many thanks!

    --Karyl

Posting Permissions

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