Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    The Toolbar that won't die (Excel 97 (SR-2))

    I'm posting here since my problem deals with Excel, but it deals with VB as well.

    I have an Excel add-in with a custom toolbar that I'm trying to update. When I distributed the add-in the first go-round users ran a little VB installer that basically copied the XLA to a specific location on the user's computer -- not XLSTART -- and installed it for them. The user opens Excel, excel loads the add-in and its toolbar -- so far, so good. I have now updated the add-in and toolbar as there are more macros and toolbar buttons. I have modified the VB installer to delete the old toolbar before installing the latest version of the add-in. I have stepped through the VB code and it deletes the old toolbar. Problem is, next time I open Excel it displays the old toolbar, not the new one even though when I go into the VBE I can see it is loading the new add-in. If I manually delete the old toolbar and de-select the add-in, close Excel, reopen it and re-select the add-in THEN the new toolbar shows up. I don't want users to have to do this manually, I want to do this through code in the VB installer, but as you can see it is giving me a dickens of a time. What am I missing here?

    TIA

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    I'm not sure what's causing your particular symptom, but I have a suggestion that might avoid it altogether. If you build the toolbar on the fly from VB code, you have better control over when and how it apears. However, I have only done this for a template, not an add-in, so I'm not sure how you will determine "when" to create the toolbar. Anyway, this is just an idea.

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    Tim

    I am running through the same problem today, updating one workbook with a new toolbar while an older toolbar exists.

    What I had to do is to delete the old toolbar from the toolbar collection, from the View Menu|Toolbars and then Customize and then delete.

    Then I save Excel and closed and went out and now my workbook displays the right toolbar. I know that you do not want to do this, but I think something needs to be reset, so maybe starting Excel via the Installer and deleteing the toolbar and then closing and then starting Excel and installing the Add-In is the way to go.

    Do you have the toolbar attached to your Add-In?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    Jim

    Sometimes I like to use some nice Button Face Images, and this is one reason that developing toolbars on the fly is hard. Yes you can have any button, but the fun of it all is to design these buttons and make them look nice, and work the way they should.

    The Toolbar creation would be the same for an add-in or a template BTW, and to determine when to create the toolbar well that depends on if the user needs it right away or later.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    Tim,

    I seriously recommend the creation of the toolbar on the fly, that is, by putting code in the Workbook_Open event and by deleting it in the Workbook_BeforeClose event. This way you will not have the problems you are mentioning. Have a look at to read about the problems that you can have trying to store toolbars and <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=30032&page=2&view =expanded&sb=5>this thread on how to create a toolbar on the fly.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    I had the same issue (no obvious way to use custom images). However, an earlier response on the board suggested that it is possible to apply a bitmap to the button, instead of using a predefined FaceID. The exising FaceID's were sutiable for my needs, so I didn't pursue it.

  7. #7
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    That's the thing, I know I can create the toolbar through code, but I would like to have custom buttom images to better convey what's going to happen when the user clicks the button.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    Maybe, you could first have a look at the over 5500 FaceIDs available in Excel 2000. See this thread on how to find the identification for the images.

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    Attached is a simple exercise for applying custom pictures to a toolbar that is generated on the fly. Open it and run TryPix macro. To apply custom pictures, you will have to paste them onto a sheet somewhere (perhaps a hidden one), copy each one to the clip board, then use the PasteFace method to paste it onto the button, for each button in sequence.

    Not too elegant, but do-able.
    Attached Files Attached Files

  10. #10
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Toolbar that won't die (Excel 97 (SR-2))

    Sorry it has taken me this long to get back to this, but I finally found a KB article that deals with this problem: "FIX Changes Made to Excel CommandBars Through Automation Are Not Saved (Q241652)". In a nutshell the article says that even though you can delete a toolbar through code, Excel doesn't save them because it doesn't think that you're the user when you close Excel. The article provides a code example of how to fool Excel into thinking you're the user by using code to create and then run a macro in a workbook to close Excel. A bit convoluted, but it works...

    Cheers,

Posting Permissions

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