Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing parameters to function assigned to button property onAction??
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"",selectio n)" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing several parameters to OnAction property. | Excel Discussion (Misc queries) | |||
Set up OnAction of a Menu Bar button to a function with variable in the parameters | Excel Discussion (Misc queries) | |||
Problem with Button OnAction property... | Excel Programming | |||
passing parameters to OnAction | Excel Programming | |||
Passing Parameters through OnAction | Excel Programming |