If you have a button on Sheet1 that executes code written behind Sheet1 and
you want to call that same code from a function or macro in another module
how would you go about doing it?

--
~ SPARKER ~

This assumes the command button was created from the control toolbox not the
forms toolbar. Your code will look something like

Private Sub CommandButton1_Click()

End Sub

Change it to

Public Sub CommandButton1_Click()

End Sub

Now you can reference it from other sheets or modules similar to

Sheet2.CommandButton1_Click

--
HTH...

Jim Thomlinson


"sparker" wrote:

If you have a button on Sheet1 that executes code written behind Sheet1 and
you want to call that same code from a function or macro in another module
how would you go about doing it?

--
~ SPARKER ~

Personally I would put the code behind the button click event into a
standard module as a subroutine. Then I would use the button click
event to call that subroutine. Then you can also call the same sub
from another function or macro.

ie, change this:

Private Sub CommandButton1_Click()
'Your code here
End Sub

to:

'In the code behind the sheet
Private Sub CommandButton1_Click()
ButtonCode
End Sub

'In a standard code module
Sub ButtonCode
'Your code here
End Sub

Then you can call the ButtonCode sub from any function of subroutine


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381403

In my opinion (for whatever that is worth) if the code for the button only
applies to this one sheet then It belongs in the sheet (where sparker has
it). If the code is more generic and could be used by many sheets and modules
then it belongs in a module. Depends entirely on the scope from my
standpoint, but I probably should have noted your solution also. Good point.
--
HTH...

Jim Thomlinson


"bhofsetz" wrote:


Personally I would put the code behind the button click event into a
standard module as a subroutine. Then I would use the button click
event to call that subroutine. Then you can also call the same sub
from another function or macro.

ie, change this:

Private Sub CommandButton1_Click()
'Your code here
End Sub

to:

'In the code behind the sheet
Private Sub CommandButton1_Click()
ButtonCode
End Sub

'In a standard code module
Sub ButtonCode
'Your code here
End Sub

Then you can call the ButtonCode sub from any function of subroutine


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381403