Returning values from OnAction

[Trooper]

I am using Excel 2003, and Visual Basic for Applications. (I've been using a
reference book written in 1994, but have looked online and through the VBA
help material extensively.)

I have managed to write code to place a custom menu on the Worksheets Menu
Bar, add two popup controls, "Go To" and "Print", and add the names of the
worksheets of the workbook to each as control buttons. But, I can't figure
out how to return values from a control. I know I can use OnAction to run a
procedure, but I want to know, for example, the Caption of the button that
was clicked. (Just getting the index number of the clicked button would
probably help, but then I'd have to figure out how to translate that index
number into the caption. But, I would be happy to know how to get both the
caption and the index, just for future reference.

Thanks!!!

 

[Peter T]

In an OnAction macro
Dim ctr as Object
Set ctr CommandBars.ActionControl

If you are sure your macro will only be called by a CommandBarButton, which
is typically though not necessarily the case, to get the intellisense
declare

Dim ctr as CommandBarButton

you can read/write properties from/to ctr, eg

Select case ctr.Caption ' or maybe ctr.Tag
case "myMacro1"
myMacro1
ctr.State = msoButtonDown


Regards,
Peter T

 

[Bob Phillips]

In the OnACtion macro, you can get immediate access to that control and its
properties via the ActionControl property

Wth Application.Commandbars.ActionControl

MsgBox .Caption

MsgBox .Tag

'etc.
End With

 

[Trooper]

Thanks. I reviewed ALL the properties (hundreds) shown on the Visual Basic
reference in the Excel help file and ActionControl is not listed! I knew
there had to be some command. I discovered that in the Object Browser,
ActionControl is shown as a property for CommandBars, but I didn't realize
that I should have been looking there. Frankly, the Help file should have
had this.

Trooper

 

[Trooper]

Thanks. I reviewed ALL the properties (hundreds) shown on the Visual Basic
reference in the Excel help file and ActionControl is not listed! I knew
there had to be some command. I discovered that in the Object Browser,
ActionControl is shown as a property for CommandBars, but I didn't realize
that I should have been looking there. Frankly, the Help file should have
had this.

Trooper

 

[Peter T]

"ActionControl" IS listed in Help under Properties "A" (or Properties "A-B"
in older versions) in Help for VBA Office 97-2003 (not sure about '2007 but
I assume it's also in that). Difficult to imagine where else it might be
better listed, where do you think it should be.

Best to think of Help as a reference manual with examples, IMO a very good
one, rather than a tutorial. That 1994 reference book of yours is more
likely to confuse than help.

Regards,
Peter T

 

[Trooper]

Hi Peter,

FYI - I just checked again on the Visual Basic Help while in Visual Basic
mode on an Excel 2003 workbook. (You mentioned you found it in the Office
help; I'm working in Excel.) The Table of Contents includes "What's New",
"Programming Concepts", "Collections", "Objects", "Methods", "Properties",
"Events", and "Enumerations". The Properties are listed under "A", "B", etc.
There is no "ActionControl" property listed. I actually thought there might
be something like "OnActionControl", but nothing like that either. (As I
said, I reviewed ALL the properties listed under all letters of the
alphabet.) If I do a search in the Help search box for "ActionControl", it
comes up, but you have to know what to look for. So, I agree that's the
place to list it. Maybe there's something screwy with my Help file. (Also,
ActionControl property should be listed under the "See Also" in the
"OnAction" help explanation.) I know there was something else that didn't
return the correct item (I can't remember what), but when I looked at it as a
property of an object in the object brower window, I got the right reference.
(It's like there's two versions of that particular help topic, with each
accessed differently. That also took hours to find because of that flaw,
because I was sent off track.) So, I am leary of finding everything in Help.
I also searched for hours and hours on the MS website for what
property/action to use to return a value from a commandbar object using
onaction, but to no avail.

Thanks again

 

[Peter T]

- Close the Help window if open (better still start in a new session of
Excel)
- On the VBE main menu click Help then "Visual Basic Help"
- This should open the main ToC with three items
- Choose "Ms Office VB Reference" which should open into "Ms Office Object
Model" (the Commandbars object belongs to the Office library)
- Expand "Properties"

The very first item under "A" of some 200 properties is your ActionControl

I agree for you to have been able to find this on your own would require you
knowing what to look for in the first place, or curiosity having seen it in
say Object Browser leading you there. That's what I meant about Help is
really more of a reference manual.

Another way to find this sort of thing, when you don't know the exact word
you need or know if it even exists, is to do exactly what you eventually
did - ask here <g>

Regards,
Peter T

 

[Trooper]

I send this just so you know your helped me greatly, but probably
inadvertently. I noticed when using the Object Browser that there's a little
note at the bottom saying what the object relates to (Office, Excel).
"AAAAAHAAAA!" I thought. That little relates to tells me what part of the
help reference to go to. I hadn't sorted that out before, so thanks!

ps - I get very stubborn on figuring out code on my own (I can almost always
do it eventually), but am just now looking at this Visual Basic stuff; have
had experience w/ Lotus 123/macros, APL, and R-Base programming. A little VB
before with Access, but not much. This object stuff is tricky. And in the
meantime, everyone's moved on to xml, whatever that is! (and Java, and html)

I'll try to put you more to the test in the future. Thanks again