Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Washington
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assigned Macros in Excel

    I have an Excel form in which I have created two custom toolbars with custom buttons. These buttons are assigned to VBA subroutines. When the original document is open, they all run perfectly. However, if I save the document as another file (e.g. Save As), the toolbar buttons in the new document work fine, but the buttons in the original document are now assigned to the macros in the new document. Thus, when you click a toolbar button in the original document, the new document opens and tries to execute the macro. Furthermore, if the original document is routed via e-mail and you click a toolbar button in the routed copy, the original document tries to open and but can't because Excel thinks it is already open.

    Can anyone explain this bizar (at least to me) behavior and how I can get around it? Because this form will be e-mailed around, I need the toolbar buttons to execute the code within that particular instance of the form, not code somewhere else. Any suggestions? I have spent much time researching the help panels and the MSN Developers website.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigned Macros in Excel

    The problem would seem to stem from the fact that you have two toolbars of the same name, but excel can only cope with one. The Save As reallocates the workbook that the active toolbar is pointing to, but you now have an attached toolbar in both workbooks. When you open the original file again, you are expecting the toolbar to relate to the original file but excel hasn't changed the pointers.

    The way round this is to dump some code into the ThisWorkbook module of your file:


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("testbar").Visible = False
    End Sub

    Private Sub Workbook_Open()
    Application.CommandBars("testbar").Visible = True
    End Sub


    You'll need to change the name to match your toolbar. I would expect this to cure your email routing problems as well - I'm not sure what's going on there but I think it's caused by the "two toolbars with the same name" thing as well. Are you by any chance emailing to yourself as a test?

    Brooke

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

    Re: Assigned Macros in Excel

    The best way to deal with toolbars is to create them on the fly when opening your workbook and delete them when closing your application. Have a look at <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 </A>

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

    Re: Assigned Macros in Excel

    He should *delete* the toolbar in the workbook_beforeclose event.

    Toolbars("TheToolBarsName").Delete
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigned Macros in Excel

    Hans is correct of course, and creating them on the fly is the way I usually deal with the situation - hence the reason for the mistake in my code - sorry about that, .Delete instead of .Visible = False works an awful lot better! I just decided that as the toolbars were already there, created and attached, that was the issue to be addressed.

    Brooke

  6. #6
    New Lounger
    Join Date
    Apr 2001
    Location
    Washington
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigned Macros in Excel

    Thanks, I will give that a try. I do have one question first: how do I specify the button images I have designed to be put on the new buttons? Do I keep them on some hidden toolbar then somehow copy them to the newly created buttons or is there another way? Thanks again for the help.

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

    Re: Assigned Macros in Excel

    There are some freeware utilities that others have created to make is easier to identify the FaceID of built-in toolbar graphics. For example,
    <A target="_blank" HREF=http://j-walk.com/ss/excel/tips/tip67.htm>http://j-walk.com/ss/excel/tips/tip67.htm</A>
    Using the FaceID's, you are limited to the extensive number of built-in pictures. As for custom graphics, someone once posted an idea for pasting a bitmap of teh appropriate size somewhere in the workbook, and referencing it when building the toolbar. Unfortunately, I never got around to trying it, but it sounded like it was at least possible.

    Meanwhile, Hans is still right about creating toolbars on teh fly. The only thing I would add is, I delete the toolbar when the workbook is deactivated, and rebuild it when it is activated. For example, suppose you have your fancy workbook in the foreground, with the toolbar shown, then switch to some other workbook; the toolbar will disappear. This makes the toolbar more "context-sensitive". It also help Excel reconcile cases where you have two different workbooks opened with the "same" toolbar; the one you see at any given time "belongs" to the active workbook.

Posting Permissions

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