Buttons in 1 module?

[jowanr]

Hello,

I have a workbook with 15 sheets. Each sheet has the same 5
commandbuttons: cb1 to cb 50. Each cb does the same thing as the cb o
the next sheet (insert row on the active sheet in a given range).

Right now I have to set a click-event for each cb, that's 50x1
click-events!
Although the click-events call a module I still have al lot of cod
that slows the program down.

Example:
Private Sub CBregel1_Click()
Call nieuweregel1
End Sub

Private Sub CBregel2_Click()
Call nieuweregel2
End Sub

And so on... And on the next sheet the same.

Is there a way to put the click-event in a module or class-module s
that I don't have to write this code on each sheet?

Any help welcome

 

[Dave Peterson]

John Walkenbach has a class module sample at:
http://j-walk.com/ss/excel/tips/tip44.htm

It's actually written for a userform, but you could modify it to use
commandbuttons on a worksheet(s).

I downloaded John's sample workbook and did this:

In a class module named Class1

Option Explicit
Public WithEvents ButtonGroup As MSForms.commandbutton
Private Sub ButtonGroup_Click()
MsgBox "Hello from " & ButtonGroup.Name & vbLf & ActiveSheet.Name
End Sub

And in a general module, I did this:

Option Explicit
Dim Buttons() As New Class1
Sub auto_open()
Dim ButtonCount As Integer
Dim OLEObj As OLEObject
Dim wks As Worksheet

' Create the Button objects
ButtonCount = 0
For Each wks In ActiveWorkbook.Worksheets
For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.commandbutton Then
ButtonCount = ButtonCount + 1
ReDim Preserve Buttons(1 To ButtonCount)
Set Buttons(ButtonCount).ButtonGroup = OLEObj.Object
End If
Next OLEObj
Next wks

End Sub

=========
Another (easier for me) is to use the button from the Forms toolbar. You can
assign any number of buttons/shapes to that same procedure (in a general
module).

In fact, depending on what you're doing, you may only need one procdure. You
may be able to figure out what should be done to what by the position of the
button or even the name of that button (or caption??).

Option Explicit
Sub testme()
Dim BTN As Button

Set BTN = ActiveSheet.Buttons(Application.Caller)

With BTN
MsgBox ActiveSheet.Name & _
vbLf & .TopLeftCell.Address & _
vbLf & .Name & _
vbLf & .Caption & _
vbLf & ActiveSheet.Name
End With

End Sub