+ Reply to Thread
Results 1 to 10 of 10

Create/Remove Custom Menu Items from Add-in

  1. #1
    Michael Malinsky
    Guest

    Create/Remove Custom Menu Items from Add-in

    I have an add-in in which I have an Auto_Open event which creates a
    custom menu item on the Worksheet Menu Bar to run other code in the
    add-in. I was reading some stuff here in the NG about the AddinInstall
    event and was wondering if that would be the more appropriate event to
    use. Would using AddinInstall cause the custom menu to automatically
    appear when the add-in is installed? Would the AddinUninstall event
    work similarly?

    Bottom line...to be "clean" I would like to have the custom menu item
    to appear when the add-in in installed via Tools>Add-ins and disappear
    when the add-in in uninstalled via Tools>Add-ins if this is even
    possible.

    Thanks for the help.

    Mike.


  2. #2
    Gary Brown
    Guest

    RE: Create/Remove Custom Menu Items from Add-in

    Why not use the 'Workbook_Open' procedure of the ThisWorkbook Object of your
    add-in to create your menu and the 'Workbook_BeforeClose' procedure to delete
    your menu?
    HTH,
    Gary Brown

    "Michael Malinsky" wrote:

    > I have an add-in in which I have an Auto_Open event which creates a
    > custom menu item on the Worksheet Menu Bar to run other code in the
    > add-in. I was reading some stuff here in the NG about the AddinInstall
    > event and was wondering if that would be the more appropriate event to
    > use. Would using AddinInstall cause the custom menu to automatically
    > appear when the add-in is installed? Would the AddinUninstall event
    > work similarly?
    >
    > Bottom line...to be "clean" I would like to have the custom menu item
    > to appear when the add-in in installed via Tools>Add-ins and disappear
    > when the add-in in uninstalled via Tools>Add-ins if this is even
    > possible.
    >
    > Thanks for the help.
    >
    > Mike.
    >
    >


  3. #3
    Rob Bovey
    Guest

    Re: Create/Remove Custom Menu Items from Add-in

    "Michael Malinsky" <[email protected]> wrote in message
    news:[email protected]...
    >I have an add-in in which I have an Auto_Open event which creates a
    > custom menu item on the Worksheet Menu Bar to run other code in the
    > add-in. I was reading some stuff here in the NG about the AddinInstall
    > event and was wondering if that would be the more appropriate event to
    > use. Would using AddinInstall cause the custom menu to automatically
    > appear when the add-in is installed? Would the AddinUninstall event
    > work similarly?
    >
    > Bottom line...to be "clean" I would like to have the custom menu item
    > to appear when the add-in in installed via Tools>Add-ins and disappear
    > when the add-in in uninstalled via Tools>Add-ins if this is even
    > possible.


    Hi Mike,

    For the purposes of adding and removing custom menus for an add-in there
    is no practical difference between the Auto_Open/Auto_Close procedures, the
    Workbook_AddinInstall/Workbook_AddinUninstall events or the
    Workbook_Open/Workbook_BeforeClose events. It's just a matter of preference.

    I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
    code behind the ThisWorkbook object. If that code ever becomes corrupted you
    may have to rebuild the whole workbook, so I do my best to leave that module
    empty.

    --
    Rob Bovey, Excel MVP
    Application Professionals
    http://www.appspro.com/

    * Take your Excel development skills to the next level.
    * Professional Excel Development
    http://www.appspro.com/Books/Books.htm



  4. #4
    Gary Brown
    Guest

    Re: Create/Remove Custom Menu Items from Add-in

    Rob,
    The reason I use Workbook_Open is because a wise man once said...
    Newsgroups: microsoft.public.excel.programming
    From: "Rob Bovey"
    Local: Tues, Jan 15 2002 12:02 pm
    Subject: Re: Number of Auto_Open (Auto_Close) Events
    "There's no problem with the concept, but Auto_Open and Auto_Close
    procedures will not fire automatically in a workbook that's been
    opened/closed from VBA."
    That got me thinking so I changed to the 'Workbook_Open' and
    Workbook_BeforeClose' procedures.
    Question: Is there more of a chance that the ThisWorkbook object will
    get corrupted than a module?
    Question: Are you recommending the Auto_Open over Workbook_Open?
    Thanks for your insights in advance. Always very interested in your
    recommendations.
    Sincerely,
    Gary Brown


    "Rob Bovey" wrote:

    > Hi Mike,
    >
    > For the purposes of adding and removing custom menus for an add-in there
    > is no practical difference between the Auto_Open/Auto_Close procedures, the
    > Workbook_AddinInstall/Workbook_AddinUninstall events or the
    > Workbook_Open/Workbook_BeforeClose events. It's just a matter of preference.
    >
    > I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
    > code behind the ThisWorkbook object. If that code ever becomes corrupted you
    > may have to rebuild the whole workbook, so I do my best to leave that module
    > empty.
    >
    > --
    > Rob Bovey, Excel MVP
    > Application Professionals
    > http://www.appspro.com/
    >
    > * Take your Excel development skills to the next level.
    > * Professional Excel Development
    > http://www.appspro.com/Books/Books.htm
    >
    >
    >


  5. #5
    Rob Bovey
    Guest

    Re: Create/Remove Custom Menu Items from Add-in

    Hi Gary,

    > The reason I use Workbook_Open is because a wise man once said...


    I'd have to go back and check out the context of that conversation, but
    yeah, it's absolutely correct that Auto_Open won't fire when you open a
    workbook from VBA whereas all the event procedures will.

    However, it's pretty trivial to manually fire the Auto_Open procedure
    for a workbook opened via VBA using the Workbook.RunAutoMacros method. And
    for me, at least, it's rare that I want anything in a workbook firing on its
    own when I open it from VBA, so this is typically the route I use (or if I
    control the code in the workbook being opened I create a special startup
    procedure that I call using Application.Run when I'm ready for it to fire).

    > Question: Is there more of a chance that the ThisWorkbook object will
    > get corrupted than a module?


    I wouldn't say there's more of a chance that one will get corrupted
    faster than the other. The critical point is that if a regular code module
    becomes corrupted, fixing it is trivial, whereas if the code-behind class
    module for the ThisWorkbook object becomes corrupted there's not much you
    can do short of rebuilding the workbook.

    > Question: Are you recommending the Auto_Open over Workbook_Open?


    Yes, there just aren't any significant advantages to using Workbook_Open
    over Auto_Open. The case for Auto_Close vs. Workbook_BeforeClose is a bit
    more complicated because Workbook_BeforeClose does give you some options
    that Auto_Close doesn't. If I do decide to use Workbook_BeforeClose, though,
    I put it in a WithEvents class module, not directly behind the ThisWorkbook
    object.

    --
    Rob Bovey, Excel MVP
    Application Professionals
    http://www.appspro.com/

    * Take your Excel development skills to the next level.
    * Professional Excel Development
    http://www.appspro.com/Books/Books.htm

    "Gary Brown" <[email protected]> wrote in message
    news:[email protected]...
    > Rob,
    > The reason I use Workbook_Open is because a wise man once said...
    > Newsgroups: microsoft.public.excel.programming
    > From: "Rob Bovey"
    > Local: Tues, Jan 15 2002 12:02 pm
    > Subject: Re: Number of Auto_Open (Auto_Close) Events
    > "There's no problem with the concept, but Auto_Open and Auto_Close
    > procedures will not fire automatically in a workbook that's been
    > opened/closed from VBA."
    > That got me thinking so I changed to the 'Workbook_Open' and
    > Workbook_BeforeClose' procedures.
    > Question: Is there more of a chance that the ThisWorkbook object will
    > get corrupted than a module?
    > Question: Are you recommending the Auto_Open over Workbook_Open?
    > Thanks for your insights in advance. Always very interested in your
    > recommendations.
    > Sincerely,
    > Gary Brown
    >
    >
    > "Rob Bovey" wrote:
    >
    >> Hi Mike,
    >>
    >> For the purposes of adding and removing custom menus for an add-in
    >> there
    >> is no practical difference between the Auto_Open/Auto_Close procedures,
    >> the
    >> Workbook_AddinInstall/Workbook_AddinUninstall events or the
    >> Workbook_Open/Workbook_BeforeClose events. It's just a matter of
    >> preference.
    >>
    >> I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
    >> code behind the ThisWorkbook object. If that code ever becomes corrupted
    >> you
    >> may have to rebuild the whole workbook, so I do my best to leave that
    >> module
    >> empty.
    >>
    >> --
    >> Rob Bovey, Excel MVP
    >> Application Professionals
    >> http://www.appspro.com/
    >>
    >> * Take your Excel development skills to the next level.
    >> * Professional Excel Development
    >> http://www.appspro.com/Books/Books.htm
    >>
    >>
    >>




  6. #6
    Michael Malinsky
    Guest

    Re: Create/Remove Custom Menu Items from Add-in

    Thanks, Rob. I used the Auto_Open/Auto_Close procedures. I did change
    my code to use the Workbook_AddinInstall/Workbook_AddinUninstall, but
    when I unselected the add-in from Tools>Add-ins, nothing happened
    unless I did something wrong. Now, with the Auto_Open/Auto_Close, the
    menu items appears/disappears as I was hoping I could do.

    Thanks again,
    Mike.


  7. #7
    Rob Bovey
    Guest

    Re: Create/Remove Custom Menu Items from Add-in

    "Michael Malinsky" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks, Rob. I used the Auto_Open/Auto_Close procedures. I did change
    > my code to use the Workbook_AddinInstall/Workbook_AddinUninstall, but
    > when I unselected the add-in from Tools>Add-ins, nothing happened
    > unless I did something wrong. Now, with the Auto_Open/Auto_Close, the
    > menu items appears/disappears as I was hoping I could do.


    Hi Mike,

    Those events definitely should have fired. The first culprit I'd check
    for is if you might have set Application.EnableEvents = False at some point
    without also setting it back to True.

    --
    Rob Bovey, Excel MVP
    Application Professionals
    http://www.appspro.com/

    * Take your Excel development skills to the next level.
    * Professional Excel Development
    http://www.appspro.com/Books/Books.htm



  8. #8
    Gary Brown
    Guest

    Re: Create/Remove Custom Menu Items from Add-in

    Thanks Rob,
    Really appreciate the feedback. That's one thing I love about this forum.
    You learn something new and/or get reminded of something virtually every day
    from great people!
    Have a good one.
    Gary Brown


    "Rob Bovey" wrote:

    > Hi Gary,
    >
    > > The reason I use Workbook_Open is because a wise man once said...

    >
    > I'd have to go back and check out the context of that conversation, but
    > yeah, it's absolutely correct that Auto_Open won't fire when you open a
    > workbook from VBA whereas all the event procedures will.
    >
    > However, it's pretty trivial to manually fire the Auto_Open procedure
    > for a workbook opened via VBA using the Workbook.RunAutoMacros method. And
    > for me, at least, it's rare that I want anything in a workbook firing on its
    > own when I open it from VBA, so this is typically the route I use (or if I
    > control the code in the workbook being opened I create a special startup
    > procedure that I call using Application.Run when I'm ready for it to fire).
    >
    > > Question: Is there more of a chance that the ThisWorkbook object will
    > > get corrupted than a module?

    >
    > I wouldn't say there's more of a chance that one will get corrupted
    > faster than the other. The critical point is that if a regular code module
    > becomes corrupted, fixing it is trivial, whereas if the code-behind class
    > module for the ThisWorkbook object becomes corrupted there's not much you
    > can do short of rebuilding the workbook.
    >
    > > Question: Are you recommending the Auto_Open over Workbook_Open?

    >
    > Yes, there just aren't any significant advantages to using Workbook_Open
    > over Auto_Open. The case for Auto_Close vs. Workbook_BeforeClose is a bit
    > more complicated because Workbook_BeforeClose does give you some options
    > that Auto_Close doesn't. If I do decide to use Workbook_BeforeClose, though,
    > I put it in a WithEvents class module, not directly behind the ThisWorkbook
    > object.
    >
    > --
    > Rob Bovey, Excel MVP
    > Application Professionals
    > http://www.appspro.com/
    >
    > * Take your Excel development skills to the next level.
    > * Professional Excel Development
    > http://www.appspro.com/Books/Books.htm
    >
    > "Gary Brown" <[email protected]> wrote in message
    > news:[email protected]...
    > > Rob,
    > > The reason I use Workbook_Open is because a wise man once said...
    > > Newsgroups: microsoft.public.excel.programming
    > > From: "Rob Bovey"
    > > Local: Tues, Jan 15 2002 12:02 pm
    > > Subject: Re: Number of Auto_Open (Auto_Close) Events
    > > "There's no problem with the concept, but Auto_Open and Auto_Close
    > > procedures will not fire automatically in a workbook that's been
    > > opened/closed from VBA."
    > > That got me thinking so I changed to the 'Workbook_Open' and
    > > Workbook_BeforeClose' procedures.
    > > Question: Is there more of a chance that the ThisWorkbook object will
    > > get corrupted than a module?
    > > Question: Are you recommending the Auto_Open over Workbook_Open?
    > > Thanks for your insights in advance. Always very interested in your
    > > recommendations.
    > > Sincerely,
    > > Gary Brown
    > >
    > >
    > > "Rob Bovey" wrote:
    > >
    > >> Hi Mike,
    > >>
    > >> For the purposes of adding and removing custom menus for an add-in
    > >> there
    > >> is no practical difference between the Auto_Open/Auto_Close procedures,
    > >> the
    > >> Workbook_AddinInstall/Workbook_AddinUninstall events or the
    > >> Workbook_Open/Workbook_BeforeClose events. It's just a matter of
    > >> preference.
    > >>
    > >> I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
    > >> code behind the ThisWorkbook object. If that code ever becomes corrupted
    > >> you
    > >> may have to rebuild the whole workbook, so I do my best to leave that
    > >> module
    > >> empty.
    > >>
    > >> --
    > >> Rob Bovey, Excel MVP
    > >> Application Professionals
    > >> http://www.appspro.com/
    > >>
    > >> * Take your Excel development skills to the next level.
    > >> * Professional Excel Development
    > >> http://www.appspro.com/Books/Books.htm
    > >>
    > >>
    > >>

    >
    >
    >


  9. #9
    Michael Malinsky
    Guest

    Re: Create/Remove Custom Menu Items from Add-in

    Rob,

    It wasn't the Application.EnableEvents that caused it since I didn't
    use it (or have never used it as I can recall). I probably used
    incorrect syntax so it wasn't recognized as being an event that should
    have fired. I may try it again since I'm now curious as to why it
    didn't work, but since it DOES work, maybe I won't mess with it.

    Thanks again for the help.


  10. #10
    Registered User
    Join Date
    03-01-2004
    Posts
    3
    I encountered one hassle in destroying the toolbar using any ofl the methods mentioned above. When there are unsaved changes in open workbooks and one attempts to close Excel, these methods are fired and the toolbar is destroyed. The excel save changes dialog prompt appears only subsequently. The user can cancel the close task and continue. However, the addin toolbar is lost.

    Regards,
    Lenin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1