PC Review


Reply
Thread Tools Rate Thread

passing parameters to function assigned to button property onAction??

 
 
Matthew Dodds
Guest
Posts: n/a
 
      13th Oct 2006
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing several parameters to OnAction property. LABKHAND Microsoft Excel Misc 1 24th Apr 2009 08:02 PM
can you index a property and then assigned those indexes a sub-property msnews.microsoft.com Microsoft C# .NET 0 19th Oct 2006 07:02 PM
Passing output parameters to SQL Server stored procedures through an SqlCommand object's Parameters collection Mark Rae Microsoft ADO .NET 8 26th Sep 2005 03:28 PM
DHCP assigned DNS server changing from assigned to 10.10.1.1 Patrick Microsoft Windows 2000 DNS 3 27th Aug 2004 04:05 AM
Re: Passing Multiple Parameters in NavigateURL property of a column Karl Microsoft ASP .NET 0 13th Aug 2004 11:59 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:24 AM.