Excel: Application.Caller & Menus


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Excel: Application.Caller & Menus

  1. #1
    Martin Rydman Guest

    Excel: Application.Caller & Menus


    Hi Gurus!

    I'm using customized menus in my app, and am trying to get to grips with
    the Application.Caller property. I'd like to have one macro responding to
    all my various menus and buttons, and then examine Application.Caller to
    rout further processing. To my dismay, I've found that if I use Begin group,
    the sperators seem to be counted along with the buttons, so when I examine
    Application.Caller(1) for a particular toolbar, it contains, say, 8 (the
    rightmost button was clicked) for a toolbar with 6 buttons and 2 separators.
    That means that I *can't* use that number to index the Controls collection.
    (BTW, I'll attach extra info to the Parameter-property to know what to do
    with the Control once I've managed to identify it...)

    Am I missing something?

    TIA

    /Martin

  2. #2
    Rob Bruce Guest

    Re: Excel: Application.Caller & Menus

    Hi Martin,

    It's a bit risky to rely on the index of the control to identify it. What if
    the user swaps the buttons/menuitems around as s/he is perfectly entitled to
    do?

    You could always make use of the Tag property of the CommandBarButton object
    to store a unique identifier for each control, but the technique I have used
    is to have the single entry routine take a parameter and assign a different
    parameter to each control:

    With cbnMyFirstMenuItem
    .Caption = "&First Menu Item"
    .OnAction = thisworkbook.name & "!'sEntry 1'"
    End With

    With cbnMySecondMenuItem
    .Caption = "&Second Menu Item"
    .OnAction = thisworkbook.name & "!'sEntry 2'"
    End With

    where cbnMyFirstMenuItem and cbnMySecondMenuItem are (obviously)
    commandbarbutton objects. Note the single quotes around the name of the
    procedure and its argument.

    HTH

    --
    Rob

    www.rb-ad.dircon.co.uk/rob/excelvba/

    Martin Rydman <martin.rydman@telia.com> wrote in message
    news:38ddd360@news.devx.com...
    >
    > Hi Gurus!
    >
    > I'm using customized menus in my app, and am trying to get to grips with
    > the Application.Caller property. I'd like to have one macro responding to
    > all my various menus and buttons, and then examine Application.Caller to
    > rout further processing. To my dismay, I've found that if I use Begin

    group,
    > the sperators seem to be counted along with the buttons, so when I examine
    > Application.Caller(1) for a particular toolbar, it contains, say, 8 (the
    > rightmost button was clicked) for a toolbar with 6 buttons and 2

    separators.
    > That means that I *can't* use that number to index the Controls

    collection.
    > (BTW, I'll attach extra info to the Parameter-property to know what to do
    > with the Control once I've managed to identify it...)
    >
    > Am I missing something?
    >
    > TIA
    >
    > /Martin




  3. #3
    Martin Rydman Guest

    Re: Excel: Application.Caller & Menus


    Hi Rob!

    A true Guru! *Where* do you dig up those obscure details? I've been on the
    Excel bandwaggon for quite some time and *never* run across the bit about
    passing parameters (and in single quotes! Not easy to guess...)

    Thanks ever so much!

    /Martin


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center