Code for pushbutton click

[krishnak]

I am working on a task that requires the creation of several charts from worksheets. Each worksheet will have the data that will produce about 10 charts with different groupings. I automated the process with buttons on the worksheet.

I have several other worksheets with different data but with a similar process of generating the charts. I have buttons on each worksheet that produce the charts with the same groupings.

The charts on worksheets have different benchmarks set.

Now I replicated the vba code for each worksheet.

Is there any way that I can have a common module for all the worksheets that has the code, but will only address the buttons on the ActiveSheet?

The buttons operate on Click events and how can I refer to the buttons on the ActiveSheet from the common module for the workbook?

Thanks in advance.

[Bob Phillips]

Have you tried Activesheet? If so, what was the problem?

[krishnak]

xld,

Thanks for the response.

My problem is illustrated best by an example.

I am attaching a Workbook. Sheet 1 and Sheet 2 contain two buttons each - one from the ActiveX controls ("Test") and the second from Form Controls ("Calculate"). I have also included a Module for the Workbook.
I want to write code in the Module for the workbook (not for individual worksheets) for Click events for these buttons.

Let us say that when we click a button on the Active sheet, the event populates Range("F4") with a value of 1250.

Whatever the Active sheet, when I click the button, it should access the code on the Module (in the workbook) and give the result. I do not want to have any code in individual worksheets.

I do not need to use both types of buttons - ActiveX and Forms Controls. Whatever suits best, I can use that type.

Thanks in advance.

[Bob Phillips]

So as I said, have you tried Activesheet? If so, what was the problem?

[krishnak]

So as I said, have you tried Activesheet? If so, what was the problem?

Here is a simple code I wrote in the Module for the workbook.

Sub btnTest_Click()
Dim oOLE As OLEObject
Set oOLE = ActiveSheet.OLEObjects("btnTest")
oOLE.Select
With Selection
ActiveSheet.Range("F4").Value = 1250
End With
End Sub

With the OLEObject, I do not find a click event. So I used the Select.
When I run the code from the Module, the result appears on the ActiveSheet. But I cannot run the code by clicking the button on the ActiveSheet.

[Bob Phillips]

With the ActiveX control you need an event procedure on each worksheet, but if you use a Forms button, you can assign all of them to the same procedure

[vba]

Sub Button_Click()
ActiveSheet.Range("F4").Value = 1250
End Sub
[/vba]

[krishnak]

OK, I am using the Forms buttons. I have one button (identical names Button2) on each worksheet.

For one worksheet (Sheet 2), the 'Click' event gives a result. For the other worksheet (Sheet 1), the button click event does not produce any result. For the first one, a 'hand' symbol appears when actuating the button. For the second one, this symbol does not appear and when the button actuated, only the button face gets selected.

How can I activate the button on the second sheet (Sheet 1)?
Is there anything I am missing?

[Bob Phillips]

Then it ain't a Forms button.

[krishnak]

Here is the trick.

When a Forms button is inserted, there will be a default macro e.g. Button1_Click. When the second button is created, another default macro name is displayed. But select the first macro (Button1_Click) for the second button as well.

This will now work for both the buttons.