Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding custom menu ONCE to control multiple files (Excel 2000)

    I've been investigating creating custom menus for Excel via code (with much help from posts on these boards!) Hoping someone can help me iron out a wrinkle in my schemes:

    I'm setting up a new spreadsheet and a new add-in file that will contain tools for manipulating data in the spreadsheet, printing bits of it, etc.; the add-in will also set up a custom menu with controls for accessing the various tools, and will delete the custom menu when the spreadsheet is closed (via the spreadsheet's Workbook_Open and Workbook_BeforeClose events).

    The spreadsheet is actually a "master" copy of the file. Users will make copies of this file, and then actually work with the copies. Each of the copy spreadsheets will therefore be attached back to the add-in, so when any one of them is opened they will get the custom menu, all of the tools, etc.

    My problem is how to handle the case where a user may open TWO or more of these copied spreadsheets at the same time. Each one will call Workbook_Open and try to create the menu, resulting in two menus; but I only want ONE menu that will control both spreadsheets. I think I have figured out how to do this, but now I have the problem where if I close either of the two (or three, or however many) sheets, it deletes the single menu -- when it is still needed by the other open sheet(s). So I'm looking for a good, reliable way for the Workbook_BeforeClose code to detect if there are any other open spreadsheets that need the menu, and only delete the menu if there are none. Any other open spreadsheet, which is not attached to this add-in, should be ignored.

    We have some other spreadsheets that already sort of have this functionality, but it's not implemented well -- basically, when a user opens the second sheet, it just adds the second menu (or third, etc.), and the extra menus never seem to get deleted, and eventually we just have to reset the whole Menu Bar to get things back to normal. I'm looking for a better way.

    Any ideas?

    Thanks!

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

    Re: Adding custom menu ONCE to control multiple files (Excel 2000)

    The point is, that your addin (which contains the code) needs to create the menu's and toolbars with it's Open event (and remove them with its Before_Close event). Your "user" files should not contain any code. This way, you can have as many "user" copies of the template file open, your addin won't care as long as you set it to work on the ActiveWorkbook (with maybe a check whether this is in fact a book that "belongs" to the addin).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom menu ONCE to control multiple files (Excel 2000)

    Ah-ha, that makes sense; but if I'm understanding correctly, it doesn't get me exactly where I'm trying to go. Please allow me to further clarify what I'm looking for:

    When someone opens the spreadsheet file, the add-in file is automatically opened in the background, because the spreadsheet has a reference set to it; but when the spreadsheet file is closed, the add-in is not closed too. It stays open in the background until Excel is completely shut down. So if a user closes all of their open spreadsheets that access the add-in, but continues to work in other spreadsheets which do not need the add-in, the menu will still be available on the toolbar. I would prefer, if possible, that the menu only be available when it is needed, i.e., when one of the special spreadsheets attached to the add-in is open.

    The only way I have thought of to do this is to keep track of each spreadsheet as it is opened and closed (not EVERY spreadsheet, but just all of the ones attached to this add-in), so that I can tell when the last one is closed and delete the menu. I think I can make something like this work, but it gets a little involved. I was looking for alternate ideas, in case there's a simpler way to do it (maybe there just isn't a simple way!)

    Thanks very much,

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adding custom menu ONCE to control multiple files (Excel 2000)

    You could declare an Application variable WithEvents in your add-in and that way you can trap the necessary Workbook Activate/Deactivate events to determine whether your toolbar needs to be visible/invisible. You might also have your add-in control the creation of all these workbooks in the first place, maintain a collection of open books and simply deactivate the toolbar if the collection is empty.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Adding custom menu ONCE to control multiple files (Excel 2000)

    It is as Rory said and you already thought: you need to create a class module to handle application events. Insert an event that handles activating/deactivating a workbook and check if your menu is needed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom menu ONCE to control multiple files (Excel 2000)

    Okay, I think I can get something to work from here. Jan Karel and Rory, thanks to you both!

Posting Permissions

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