passing parameters to function assigned to button property onAction??

Discussion in 'Microsoft Excel Programming' started by Matthew Dodds, Oct 13, 2006.

  1. This one has me stumped. Concise query immediately below. Rather longer
    background follows.


    Concise query:
    1. How can I unambiguously identify a msoControlButton calling a macro?
    Or ...
    2. How can I supply parameters to a macro assigned to the .OnAction
    property of a button


    Background to query:
    I am building an application for charting data. Since the data are
    generated
    by a custom DLL Addin in Excel, I have appended the control buttons for
    this
    application onto the Addin custom menu item on the Worksheet Menu Bar.

    The structure of these additions is
    - msoControlPopup; 'charts'
    -1 msoControlButton; chart the data
    -2 msoControlButton; delete the charts
    - msoControlPopup; 'export charts'
    -3 msoControlButton; As GIFs
    -4 msoControlButton; As PNGs
    -5 msoControlButton; As JPEGs
    -6 msoControlButton; To PPT

    The code for each onAction property works OK independent of the
    controlButtons.

    However my attempt to pass parameters to the export functions using
    ..OnAction = "exportit.exportChtObjAsImage(""PNG"",selection)" failed;
    obvious really,
    there being no macro of this name. Is there another way I can call a
    function using the
    ..OnAction property AND supply parameters to that function??


    Earlier contributions to this forum indicated that the
    Application.Caller property
    returns the name of the calling button. I have therefore been exploring
    this property
    with the idea of supplying the appropriate parameters post hoc.

    However I cannot recover a button name or caption from the variant
    array returned.
    This has Lbound=1, Ubound=4. Querying it using (i is a long)

    For i = LBound(Application.Caller) To UBound(Application.Caller)
    Debug.Print i & ") " & TypeName(Application.Caller(i)) & ", value
    is " & Application.Caller(i)
    Next i

    returns

    1) Double, value is 1
    2) Double, value is 8 <:this is the index position of Addin custom
    menu
    3) Double, value is 10
    and an error for i=4

    Oddly, if the array is assigned to myVar (dim as variant) and queried
    using

    myVar = Application.Caller
    For i = LBound(myVar) To UBound(myVar)
    Debug.Print i & ") " & TypeName(myVar(i)) & ", value is " &
    myVar(i)
    Next i

    returns

    1) Double, value is 1
    2) Double, value is 8 <:this is the index position on the
    CommandBars("Worksheet Menu Bar"
    3) Double, value is 10
    4) Double, value is 4 ..... i.e. get a value for myVar(4) but not
    Application.Caller(4). Go figure.

    So far a bit strange but apparently promising. However running this
    assignment to myVar and
    querying for each of the buttons yields

    button 1 (chart data)
    1) Double, value is 1 **
    2) Double, value is 8
    3) Double, value is 10
    4) Double, value is 4
    button 2 (delete charts)
    1) Double, value is 2 **
    2) Double, value is 8
    3) Double, value is 10
    4) Double, value is 4

    button 3 (Export As GIF)
    1) Double, value is 1 **
    2) Double, value is 8
    3) Double, value is 10
    4) Double, value is 4
    button 4 (Export As PNG)
    1) Double, value is 2 **
    2) Double, value is 8
    3) Double, value is 10
    4) Double, value is 4
    button 5 (Export As JPEG)
    1) Double, value is 3 **
    2) Double, value is 8
    3) Double, value is 10
    4) Double, value is 4
    button 6 (Export to PPT)
    1) Double, value is 4 **
    2) Double, value is 8
    3) Double, value is 10
    4) Double, value is 4

    Other than the incrementing myVar(1) value (**) for successive buttons
    on the same msoControlPopup the information content of the return from
    the Application.Caller property
    is low - it certainly doesn't constitute an unambiguous identification
    of the calling button

    So, concisely ...
    1. How can I unambiguously identify a msoControlButton calling a macro?

    Or ...

    2. How can I supply parameters to a macro assigned to the .OnAction
    property of a button?

    Suggestions, please, as to how I can deal with this. Many thanks in
    advance.

    Matthew
     
    Matthew Dodds, Oct 13, 2006
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark Bigelow

    Passing Parameters through OnAction

    Mark Bigelow, Sep 9, 2003, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    220
    Tom Ogilvy
    Sep 10, 2003
  2. Grant Reid

    Button Selection OnAction property

    Grant Reid, Jun 23, 2004, in forum: Microsoft Excel Programming
    Replies:
    7
    Views:
    2,053
    Dave Peterson
    Jun 26, 2004
  3. Michel Pierron

    Re: passing parameters to OnAction

    Michel Pierron, Sep 8, 2004, in forum: Microsoft Excel Programming
    Replies:
    8
    Views:
    225
    Guest
    Oct 29, 2004
  4. fausto

    Problem with Button OnAction property...

    fausto, Feb 25, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    229
    fausto
    Feb 26, 2005
  5. Ray C

    Single quotes in OnAction property of a button

    Ray C, Apr 7, 2008, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    191
Loading...

Share This Page