Application.Caller

[Mark Worthington]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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!

 

[Bob Phillips]

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

 

[Dave Peterson]

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.


<<snipped>>

 

[Mark Worthington]

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]

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]

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