Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
I'm wresting with an issue regarding the Caller property. I have used
it to identify what buttons on a worksheet have been clicked, as follows : Sub My_Application_Caller() ' Application.Caller responds with the name of the control that was activated ' ie, the button that was pressed. This enables the assigned button name to be ' used in the macro as a variable. MsgBox Application.Caller Select Case Application.Caller Case "Button_Application_Caller" MsgBox "Macro called directly by the " & Application.Caller & " button!" Case Else ' Some other method MsgBox "Macro called via the Procedure, " & Application.Caller & " button!" End Select End Sub (On the side, even if I use a button to call another procedure which itself calls the procedure My_Application_Caller, the originating button call is remembered. Naming the buttons xxx_1, xxx_2 etc enables the assigned button name to be easily used in a procedure as a variable). The problem is trying apply this to Toolbar buttons. It may not be possible, but I would welcome any assistance. The MsgBox gives "Run Time Error 13, Type Mismatch" which sounds pretty conclusive (probably the #REF! error value). When I used the macro recorder to record copying a toolbar button from one bar to another, it gave me : Application.CommandBars("Custom Popup 7255480").Controls(10).Copy Bar:= _ Application.CommandBars("Custom Popup 7255539") which may indicate something to someone! Any help would be much appreciated, Regards, Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
Mark,
Presumably you are trying to have a generic piece of code that determines what fired it? For toolbar buttons, you need code like With Application.CommandBars.ActionControl ' End With you can then test any of its properties such as Caption , Id , etc.. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark Worthington" wrote in message om... I'm wresting with an issue regarding the Caller property. I have used it to identify what buttons on a worksheet have been clicked, as follows : Sub My_Application_Caller() ' Application.Caller responds with the name of the control that was activated ' ie, the button that was pressed. This enables the assigned button name to be ' used in the macro as a variable. MsgBox Application.Caller Select Case Application.Caller Case "Button_Application_Caller" MsgBox "Macro called directly by the " & Application.Caller & " button!" Case Else ' Some other method MsgBox "Macro called via the Procedure, " & Application.Caller & " button!" End Select End Sub (On the side, even if I use a button to call another procedure which itself calls the procedure My_Application_Caller, the originating button call is remembered. Naming the buttons xxx_1, xxx_2 etc enables the assigned button name to be easily used in a procedure as a variable). The problem is trying apply this to Toolbar buttons. It may not be possible, but I would welcome any assistance. The MsgBox gives "Run Time Error 13, Type Mismatch" which sounds pretty conclusive (probably the #REF! error value). When I used the macro recorder to record copying a toolbar button from one bar to another, it gave me : Application.CommandBars("Custom Popup 7255480").Controls(10).Copy Bar:= _ Application.CommandBars("Custom Popup 7255539") which may indicate something to someone! Any help would be much appreciated, Regards, Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
For toolbar buttons, you need code like
With Application.CommandBars.ActionControl ' End With you can then test any of its properties such as Caption , Id , etc.. Bob, You're the man! Yes, this will enable me to finally simplify many procedures that do nearly the same thing. Trawling through the Help is not always productive, but your guidance about the AtionControl enabled me to track down the relevant property for UserForm controls, namely ActiveControl. This may well prove very useful to know, many thanks! Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
Mark,
Well, it is clear that I was wrong about what you originally wanted to do, and I admit that I am lost as to what you are trying to do. But hey, what the heck, you are sorted now, and even better, you did most of it yourself, all I did was prod you a bit. Regards Bob "Mark Worthington" wrote in message om... For toolbar buttons, you need code like With Application.CommandBars.ActionControl ' End With you can then test any of its properties such as Caption , Id , etc.. Bob, You're the man! Yes, this will enable me to finally simplify many procedures that do nearly the same thing. Trawling through the Help is not always productive, but your guidance about the AtionControl enabled me to track down the relevant property for UserForm controls, namely ActiveControl. This may well prove very useful to know, many thanks! Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
Presumably you are trying to have a generic piece of code that
determines what fired it? Well, it is clear that I was wrong about what you originally wanted to do,and I admit that I am lost as to what you are trying to do. But hey, what the heck, you are sorted now, and even better, you did most of it yourself, all I did was prod you a bit. Bob, Sorry I didn't answer your query I try not to overload my posts & bore people! Yes, generic is always on my mind (why repeat stuff?), but also it's a case of doing something because it can be done .. it's not always the best way, but it all helps in the wider understanding of Excel VBA. I once wrote an Excel version of a simple game called Blockade, & used normal buttons (Button_1, Button_2) with Application.Caller to drive the code. Many years later I applied it to a work problem, so it proved useful in the end. As to what I'm up to, well, playing, some people say! I have a number of "shortcut" macros in Personal.xls & therefore Excel.xlb, each associated with a toolbar button. As you can imagine, there's a lot of repeated code. Now that I write better code, I got into making improvements and so to the Caller question. Generic groups of toolbar buttons (say those associated with viewing) could call a single procedure, and rather than specify a global variable to transfer the required info, I thought why not just use the "caller" technique. With the Select Case, it would be easy to remove all the repeated code that fills up my Personal.xls. As usual, there are many ways to do anything in Excel. I have been developing the technique given by John Walkenbach for programmatically creating UserForms at run-time. (I'm a big fan of JW's books). This seems so neat, it's a shame not to use it somewhere. That lead on to my questioning how to apply the "caller" to controls : I want to have a generic OptionButton style UserForm (modeless of course) that can be used for chart line colours, for various printing options .. .whatever. The usual way seems to be an "OK" button which checks for the state of things on the UserForm and acts accordingly. I would like to have a simple line of code for each OptionButton that just calls my master procedure which selects by case as determined by the calling control. This way its a single mouse click and no need for global variables. Just an idea. Is there any reason why serious code can't be in the UserForm module, anyway? I'm new to UserForms On the issue of programmatically creating UserForms at run-time : it isn't suitable for my Personal.xls as I keep that protected, and I wanted to avoid Refrences. That lead on to my reading up on Add-Ins see how things soon spiral almost out of control! As a matter of interest, John uses this line to populate an array : For i = 1 To Cnt Ops(i) = Range("Animals").Range("A1").Offset(i - 1, 0) Next i I know what is happening, but I can't work out the exact mechanism. Can you help, please? Cheers! Mark *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
Mark,
Lol! I was trying to write up a wrap-up post, and give you a pat for solving it yourself. I wasn't chiding you for not answering me, but now that you have, thanks, it is nice to know what people are trying to achieve. Regards Bob "Mark Worthington" wrote in message ... Presumably you are trying to have a generic piece of code that determines what fired it? Well, it is clear that I was wrong about what you originally wanted to do,and I admit that I am lost as to what you are trying to do. But hey, what the heck, you are sorted now, and even better, you did most of it yourself, all I did was prod you a bit. Bob, Sorry I didn't answer your query . I try not to overload my posts & bore people! Yes, generic is always on my mind (why repeat stuff?), but also it's a case of doing something because it can be done ... it's not always the best way, but it all helps in the wider understanding of Excel VBA. I once wrote an Excel version of a simple game called Blockade, & used normal buttons (Button_1, Button_2) with Application.Caller to drive the code. Many years later I applied it to a work problem, so it proved useful in the end. As to what I'm up to, well, playing, some people say! I have a number of "shortcut" macros in Personal.xls & therefore Excel.xlb, each associated with a toolbar button. As you can imagine, there's a lot of repeated code. Now that I write better code, I got into making improvements and so to the Caller question. Generic groups of toolbar buttons (say those associated with viewing) could call a single procedure, and rather than specify a global variable to transfer the required info, I thought why not just use the "caller" technique. With the Select Case, it would be easy to remove all the repeated code that fills up my Personal.xls. As usual, there are many ways to do anything in Excel. I have been developing the technique given by John Walkenbach for programmatically creating UserForms at run-time. (I'm a big fan of JW's books). This seems so neat, it's a shame not to use it somewhere. That lead on to my questioning how to apply the "caller" to controls : I want to have a generic OptionButton style UserForm (modeless of course) that can be used for chart line colours, for various printing options .. whatever. The usual way seems to be an "OK" button which checks for the state of things on the UserForm and acts accordingly. I would like to have a simple line of code for each OptionButton that just calls my master procedure which selects by case as determined by the calling control. This way it's a single mouse click and no need for global variables. Just an idea. Is there any reason why serious code can't be in the UserForm module, anyway? I'm new to UserForms . On the issue of programmatically creating UserForms at run-time : it isn't suitable for my Personal.xls as I keep that protected, and I wanted to avoid Refrences. That lead on to my reading up on Add-Ins . see how things soon spiral almost out of control! As a matter of interest, John uses this line to populate an array : For i = 1 To Cnt Ops(i) = Range("Animals").Range("A1").Offset(i - 1, 0) Next i I know what is happening, but I can't work out the exact mechanism. Can you help, please? Cheers! Mark *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
just this portion:
As a matter of interest, John uses this line to populate an array : For i = 1 To Cnt Ops(i) = Range("Animals").Range("A1").Offset(i - 1, 0) Next i I know what is happening, but I can't work out the exact mechanism. Can you help, please? Some place in John's sample code are more lines: dim i as long dim Cnt as long dim ops() as variant cnt = range("animals").cells.count 'I'm assuming a single area, single column range (like B12:B21) redim ops(1 to cnt) 'make ops have cnt elements--same as the number of 'cells in Range("animals") for i = 1 to cnt ops(i) = range("animals").range("a1").offset(i-1,0) next i The top left cell in the range("animals") can be refered to as: range("animals").range("a1") Range("animals").range("b2") is one down and one to the right from that topleftcell. When you do the offset bit, the first time through, i = 1. ..offset(1-1,0) is the same as .offset(0,0) which is that first cell in the range. and stuff it into ops(1) Second time through: ..offset(2-1,0) is .offset(1,0) means come down one row but stay in the same column And plop that into ops(2) And so forth. Mark Worthington wrote: <<snipped -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
Bob,
Of course I knew you weren't chiding me! I really appreciated the help. As for : The top left cell in the range("animals") can be referred to as: range("animals").range("a1") It took a while to get my head around this. I am used to the Cells property, and it took a while to realise that these do the same thing : For i = 1 To Cnt Value = Range("My_Range").Range("A1").Offset(i - 1, 0).Value Value = Range("My_Range").Cells(i, 1).Value Next i Two points : the Cells property "offset" is different to that used in Offset, and I always like to use the .Value property rather than use the default (I don't suppose it matters too much and it makes the code it easier to follow). I much prefer the Cells property .. the Range object returned by the Cells property is relative to the upper left cell of the referenced Range. Regards, Mark |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
I could have posted his link for your reading pleasure. It's by Alan Beban on
Chip Pearson's web site: http://www.cpearson.com/excel/cells.htm It might help understand some addressing techniques. Mark Worthington wrote: Bob, Of course I knew you weren't chiding me! I really appreciated the help. As for : The top left cell in the range("animals") can be referred to as: range("animals").range("a1") It took a while to get my head around this. I am used to the Cells property, and it took a while to realise that these do the same thing : For i = 1 To Cnt Value = Range("My_Range").Range("A1").Offset(i - 1, 0).Value Value = Range("My_Range").Cells(i, 1).Value Next i Two points : the Cells property "offset" is different to that used in Offset, and I always like to use the .Value property rather than use the default (I don't suppose it matters too much and it makes the code it easier to follow). I much prefer the Cells property .. the Range object returned by the Cells property is relative to the upper left cell of the referenced Range. Regards, Mark -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Caller
I could have posted his link for your reading pleasure. It's by Alan Beban on
Chip Pearson's web site: http://www.cpearson.com/excel/cells.htm It might help understand some addressing techniques. Dave, Many thanks, the Item property is indeed hidden away .... and interesting, too. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Caller | Excel Discussion (Misc queries) | |||
Row = Application.Caller.Row | Excel Worksheet Functions | |||
application.caller | Excel Programming | |||
Application.caller | Excel Programming | |||
DDE and application.caller help | Excel Programming |