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

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" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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.



Matt Jensen wrote:

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

--

Dave Peterson

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" wrote in message
...
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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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