Hello,

I have a workbook with a number of sheets (over 100) and I would like to add
a commandbutton to the top of each sheet. The command button would have the
same functionality on each sheet. I have written the code to create the
button in a class module, and set up a loop to create the button on every
worksheet. I have written the code to run when the button is clicked in a
code module. Is there a way to assign a procedure to the click event of the
button without going into the code module for each worksheet and writing the
same call 100 times? The embedded button does not have an 'OnAction'
property or anything similar which can be set. I'm thinking there must be a
way to implement this in the class definition. I am aware that I could
create a toolbar or add a button to an existing toolbar or dropdown menu, but
the target audience for this workbook is not all that savvy and I would like
to make the functionality patently obvious to anyone who looks at this
workbook.

Thanks for any ideas

The toolbar is so much simpler. One button, just write the code for the
activesheet.

HTH

Bob

"Little Elephant with Big Ears"
om wrote in message
...
Hello,

I have a workbook with a number of sheets (over 100) and I would like to
add
a commandbutton to the top of each sheet. The command button would have
the
same functionality on each sheet. I have written the code to create the
button in a class module, and set up a loop to create the button on every
worksheet. I have written the code to run when the button is clicked in a
code module. Is there a way to assign a procedure to the click event of
the
button without going into the code module for each worksheet and writing
the
same call 100 times? The embedded button does not have an 'OnAction'
property or anything similar which can be set. I'm thinking there must be
a
way to implement this in the class definition. I am aware that I could
create a toolbar or add a button to an existing toolbar or dropdown menu,
but
the target audience for this workbook is not all that savvy and I would
like
to make the functionality patently obvious to anyone who looks at this
workbook.

Thanks for any ideas

Without redesigning your whole effort, I'll give you a simple process to put
a 'button' on a sheet and assign code to it's click event:

Simply use the Text Box from the Excel Draw toolbar. Put the text box on
the sheet and use it's format to pretty it up with some color and a border,
size it and set it to not resize or move when cells around it are resized or
moved.

Now use its' Assign Macro option (right click on it to get to these options)
and if you already have a Sub written, just assign that as the macro to run
when it's clicked. Otherwise you can copy your code into its own _Click
event with the NEW option that is available when you choose Assign Macro.

The beauty of this rather simple solution is that you can make it patently
obvious by putting all the words you want into the text box such as "CLICK ME
to add all the monthly costs, place them on the summary sheet and provide an
average of indirect costs for use in our annual budget planning" if you want
to go to that level.

Since your button as the same functionality on each sheet, you can simply
copy and paste it to each of the other sheets, either as something to do
while watching a rerun of NCIS or write some code to do it for you.

"Little Elephant with Big Ears" wrote:

Hello,

I have a workbook with a number of sheets (over 100) and I would like to add
a commandbutton to the top of each sheet. The command button would have the
same functionality on each sheet. I have written the code to create the
button in a class module, and set up a loop to create the button on every
worksheet. I have written the code to run when the button is clicked in a
code module. Is there a way to assign a procedure to the click event of the
button without going into the code module for each worksheet and writing the
same call 100 times? The embedded button does not have an 'OnAction'
property or anything similar which can be set. I'm thinking there must be a
way to implement this in the class definition. I am aware that I could
create a toolbar or add a button to an existing toolbar or dropdown menu, but
the target audience for this workbook is not all that savvy and I would like
to make the functionality patently obvious to anyone who looks at this
workbook.

Thanks for any ideas

This is probably what I will do, although I am still wondering why the code
belongs to the worksheet and not the object. Also, Procedures cannot be
passed as argumets? or returned by functions?

"JLatham" wrote:

Without redesigning your whole effort, I'll give you a simple process to put
a 'button' on a sheet and assign code to it's click event:

Simply use the Text Box from the Excel Draw toolbar. Put the text box on
the sheet and use it's format to pretty it up with some color and a border,
size it and set it to not resize or move when cells around it are resized or
moved.

Now use its' Assign Macro option (right click on it to get to these options)
and if you already have a Sub written, just assign that as the macro to run
when it's clicked. Otherwise you can copy your code into its own _Click
event with the NEW option that is available when you choose Assign Macro.

The beauty of this rather simple solution is that you can make it patently
obvious by putting all the words you want into the text box such as "CLICK ME
to add all the monthly costs, place them on the summary sheet and provide an
average of indirect costs for use in our annual budget planning" if you want
to go to that level.

Since your button as the same functionality on each sheet, you can simply
copy and paste it to each of the other sheets, either as something to do
while watching a rerun of NCIS or write some code to do it for you.

"Little Elephant with Big Ears" wrote:

Hello,

I have a workbook with a number of sheets (over 100) and I would like to add
a commandbutton to the top of each sheet. The command button would have the
same functionality on each sheet. I have written the code to create the
button in a class module, and set up a loop to create the button on every
worksheet. I have written the code to run when the button is clicked in a
code module. Is there a way to assign a procedure to the click event of the
button without going into the code module for each worksheet and writing the
same call 100 times? The embedded button does not have an 'OnAction'
property or anything similar which can be set. I'm thinking there must be a
way to implement this in the class definition. I am aware that I could
create a toolbar or add a button to an existing toolbar or dropdown menu, but
the target audience for this workbook is not all that savvy and I would like
to make the functionality patently obvious to anyone who looks at this
workbook.

Thanks for any ideas