button onclick event

[RateTheBuilder]

Hi

I have a spreadsheet with 6 worksheets, each of which has a button with
corresponding onClick event code.

In my final worksheet I have a button that when I click I want it to cause
each of the onClick events of the other worksheets buttons to fire.

Anyone any clues or sites I can view to help me do this?

Thanks in advance

Brendan
______________________________________________
Rate your experiences with your UK and Ireland builders at
http://www.ratethebuilder.co.uk
=========================================

 

[Jim Rech]

You can call commandbutton handler code in another sheet's module if you
call it with the codename of that module:

Sheet2.CommandButton1_Click

and that sub is not declared Private.

Perhaps a better way is for all the button_click subs to call a common
routine in a standard module:

Private Sub CommandButton1_Click()
CommonSub Me
End Sub


in a standard module:

Sub CommonSub(WS As Worksheet)
MsgBox "Called from " & WS.Name
End Sub

and then the last sheet's button can call that multple times:

CommonSub Sheet1
CommonSub Sheet2
etc.

--
Jim
| If what I want cannot be done, is it possible to call a function in one
| sheet from a button click of a different sheet?
|
| Thanks
|
| Brendan
| ______________________________________________
| Rate your experiences with your UK and Ireland builders at
| http://www.ratethebuilder.co.uk
| =========================================
| | > Hi
| >
| > I have a spreadsheet with 6 worksheets, each of which has a button with
| > corresponding onClick event code.
| >
| > In my final worksheet I have a button that when I click I want it to
cause
| > each of the onClick events of the other worksheets buttons to fire.
| >
| > Anyone any clues or sites I can view to help me do this?
| >
| > Thanks in advance
| >
| > Brendan
| > ______________________________________________
| > Rate your experiences with your UK and Ireland builders at
| > http://www.ratethebuilder.co.uk
| > =========================================
| >
|
|

 

[Dave Peterson]

I created a couple of worksheets and added a commandbutton from the Control
Toolbox toolbar to each. Each button was named CommandButton1.

And each button had this code assigned to it:

Option Explicit
Sub CommandButton1_Click()
MsgBox "hi from: " & Me.Name
End Sub

Notice that "Private" was removed.

On the last sheet, I had this code in the _Click event:

Option Explicit
Private Sub CommandButton1_Click()
Call Sheet1.CommandButton1_Click
Call Sheet2.CommandButton1_Click
End Sub

Sheet1 and Sheet2 are the codenames for the worksheet--not the worksheet name
that you see in the tab in excel.

But if I wanted to use that sheetname, I could use:

Option Explicit
Private Sub CommandButton1_Click()
Call Worksheets("sheetname here").CommandButton1_Click
Call Worksheets("another sheet name").CommandButton1_Click
End Sub