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
"Peter T" wrote:
- 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" wrote in message
...
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" wrote:
"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" wrote in message
...
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" wrote:
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
"Trooper" wrote in message
...
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!!!