Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
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 -- __________________________________ HTH Bob "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!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
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!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
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 "Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "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!!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
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!!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
- 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!!! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning values from OnAction
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif using Values, but returning text or values | Excel Discussion (Misc queries) | |||
Mactching Values & Returning Values | Excel Programming | |||
Sorting unique values and returning values from a formula | Excel Programming | |||
Returning Values | Excel Programming | |||
returning values | Excel Programming |