Returning values from OnAction
"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!!!
|