passing parameters to function assigned to button property onAction??

[Matthew Dodds]

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