Universal procedure for changes

[Matt Jensen]

Howdy
I've got a variety of Control forms in my worksheet.
In the change event of a number of these form elements, I need to copy the
new value to another worksheet.
Can I write one procedure for this and inside this proc use something like
"me.value" to referring to the calling elements value? or do I have to write
out code for each and every form elements who's change I want to capture?
Thanks
Matt

 

[Bob Phillips]

Matt,

You can't use me as that will refer to a level above the control, such as
the form, or the worksheet.

What you can try is to create a class to handle multiple controls. John
Walkenbach has a demo on his site at
http://j-walk.com/ss/excel/tips/tip44.htm. This demo is for commandbuttons,
but play with and try other controls.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Matt Jensen]

Cool thanks Bob!
So am I right in saying that what you and John are saying is that a
procedure can't know the name of 'element' that called it without using a
class?
I don't understand why you can't use that ShowDialog() code as a normal Sub
without the class? There is some fundamental principle I'm missing it
seems... Does the example only apply to a userform? My labels are embedded
in my worksheet if that makes a difference...
Cheers
Matt

 

[Dave Peterson]

You may want to consider using the controls from the Forms toolbar. You may be
able to assign each the same macro and deterime what should happen to what in
code.

 

[Bob Phillips]

Matt,

When you mentioned control forms, I thought you meant userforms. This
technique is for userforms.

If you have buttons from the forms toolbar on the worksheet, you can assign
them all to the same macro, and test Application.Caller to find out which
button called the macro.

If it is control toolbox buttons, you have a bigger job.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Matt Jensen]

Yeah they are control toolbox buttons, that was decided a while back as they
needs to be that way. I did see a similar example by Tom Olgilvy when doing
a google groups search of this which should suffice for solving it, however
I still don't understand the fundamental problems I detailed in my second
post...
Matt