Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Buttons in 1 module?
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 -- jowan ----------------------------------------------------------------------- jowanr's Profile: http://www.excelforum.com/member.php...fo&userid=1688 View this thread: http://www.excelforum.com/showthread.php?threadid=32060 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Buttons in 1 module?
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 jowanr wrote: Hello, I have a workbook with 15 sheets. Each sheet has the same 50 commandbuttons: cb1 to cb 50. Each cb does the same thing as the cb on 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 50x15 click-events! Although the click-events call a module I still have al lot of code 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 so that I don't have to write this code on each sheet? Any help welcome! -- jowanr ------------------------------------------------------------------------ jowanr's Profile: http://www.excelforum.com/member.php...o&userid=16886 View this thread: http://www.excelforum.com/showthread...hreadid=320602 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CLASS MODULE & SIMPLE MODULE | Excel Discussion (Misc queries) | |||
have toggle buttons but everytime print preview buttons move | Excel Discussion (Misc queries) | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
arrays - module to module | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |