Copy a command button

  • Thread starter Thread starter a m spock
  • Start date Start date
A

a m spock

On a worksheet I have command buttons to activate sheet specific macros. How
do I copy them to other sheets in the same workbook to perform the same task
for that sheet. Currently I use ctrl x to activate the macros.
 
When you say Sheet specific macros I assume that you mean the command buttons
are ActiveX controls either from the Controls Toolbox toolbar in pre xl2007
versions or from the ActiveX buttons in xl2007. If so then you can place the
main part of the code in a standard module and call the code from the command
button code.

Example;
In the standard module insert code as
Sub MyMacroCode()

'your code here

End Sub

the button code for each sheet would be like this:
Private Sub CommandButton1_Click()
Call MyMacroCode
End Sub

The code in the standard module needs to specifically address the worksheet
that it is intended to process. If selecting the sheets then ActiveSheet can
be used otherwise you can use a parameter in the calling code like this

Call MyMacroCode(Activesheet.Name)

and the sub in the standard module would be like this
Sub MyMacroCode(strShtName)
You then use strShtName in lieu of the actual sheet name in your code like
this.

Sheets("Sheet1").Range("A1")
becomes this
Sheets(strMyShtName).Range("A1")

Note no quotes around the variable strMyShtName.

Another option to the above is to use a Forms button (from the Forms toolbar
in pre xl2007 or from the forms controls in xl2007) and place the code in a
standard module and you can copy that button to all sheets and the button
always calls the same code. I personally don't like the method but I guess
not everyone would agree with me.
 
Back
Top