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!