Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Menu Item in xla (Excel 2000)

    For some reason not known to me this add-in creates the toolbar item everytime without deleting it when I close Excel. Please help

    Thanks
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Menu Item in xla (Excel 2000)

    Could you explain how the AddIn is opened when needed, and how it is closed when finished.

    There is code to delete the menu entry on closing th eworkbook, which seems to work fine. If you have the workbook stored in your XLStart directory, then it will open and recreate the menu item each time you open Excel, which may make it look like it is not deleting it on closing. Or perhaps the workbook is included in your registered AddIns (Tools, AddIns), in which case much the same situation applies. If this is the case, and you check and uncheck the addIn as you require, the following aditional code might help.

    Private Sub Workbook_AddinUninstall()
    Call Delete_MenuItem
    End Sub

    I have included that code in your file and re-attach it.

    Andrew C
    Attached Files Attached Files

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

    Re: Menu Item in xla (Excel 2000)

    It works OK for me (in Excel 97):
    - If I open the .xla directly, the menu item is created; when I quit and restart Excel, the menu item is absent.
    - If I install the .xla as an add-in, the menu item is there each time - this is as intended, for the .xla is opened with Excel, so the menu item is created anew.

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Item in xla (Excel 2000)

    The that file was originally used as a workbook (not an xla). The delete menu item is triggered by a before close event. Since the xla is not closing, the event is not getting triggered. To work around it you could:

    1. Select a different event to trigger the automatic deletion
    2. Instruct users to detach the add-in when they are done.
    3. Call the Delete_MenuItem at the end of your telReport macro
    4. Add a control to your custom menu called "Close Report Mode" that will delete the Custom menu (file attached)

    There are probably other ways, but I have not worked with xla files too much.

    Leigh <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Item in xla (Excel 2000)

    Thanks for all the suggestions. The answer was very easy after some guidance. I deleted the workbook-open and workbook_close actions and replace it with the following:

    Private Sub Workbook_AddinInstall()
    Call Create_MenuItem
    End Sub

    Private Sub Workbook_AddinUninstall()
    Call Delete_MenuItem
    End Sub

    It works like a dream

    Thanks again for all the help

Posting Permissions

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