multi instance same embedded bttn using same code

[neiljabba]

Hello all,

I am using a multiple worksheet workbook in Excel with a number of common commandbuttons embedded on each sheet. Is there a method of assigning the same code to the instance of that button no matter which sheet its on.

I can get the code to run in the ThisWorkbook object as a piece of code related to the ActiveSheet but not assign it to the button to work thats embedded on the various sheets.

Im trying to avoid having to add code to each Sheets object referring to each button it seems a bit longwinded to be efficient. There must be a better way.

I suppose the key problem is that they are embedded. I have thought of a new toolbar but Im designing this for a range of users and feel the button is best placed in its relevant position.

How do i do this please?

Many thanks in advance.

Neil

 

[VBAjedi]

Maybe I'm missing something here, but why can't you:

1) Put your code in a sub in a normal module
Sub MyCode()
' Do stuff
End Sub

2) Put the following call in the MyButton_Click event of each worksheet that has the button:

MyCode

That would be how I normally do it. . .


VBAjedi

 

[neiljabba]

Cheers VBAjedi Ill give it a go

Thanks again

Neil

 

[krinid]

And if you do it for one button (using VBAjedi's technique), just copy and paste the button onto other sheets and you're set. (If copy/paste is an option... not sure of all your buttons look the same, etc). If you really have a lot of sheets (or even just a few sheets with a lot of buttons) you should consider putting the buttons onto the sheet using code.