CheckBox code repetition

[janeyjamjar]

Afternoon all,

I have a sheet in Excel with 66 checkboxes (all necessary!) and I was wondering if there is any way to avoid 66 subs that the checkboxes are "attached" to. They are all required to call the same function so I have 66 subs each with three lines like this...

[VBA]sub checkbox1_click()
call fnSomeFunction
end sub[/VBA]

Any help greatly appreciated.

Janey

[Bob Phillips]

Insert a class modules with this code

[vba]

Option Explicit

Public WithEvents mCheckBoxGroup As msforms.CheckBox

Private Sub mCheckBoxGroup_Click()
Call fnSomeFunction
End Sub
[/vba]

and name it clsUserFormmEvents, then add this code to your form

[vba]
Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cBtnEvents As clsUserFormEvents
Dim ctl As msforms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mCheckBoxGroup = ctl
mcolEvents.Add cBtnEvents
End If
Next

End Sub
[/vba]

[JimmyTheHand]

Hi Bob,

How would you go if you had the checkboxes on a worksheet (i.e. not form)? And if the checkboxes were all shapes by object type?

Jimmy

[Bob Phillips]

Straight from my code library Jimmy

[vba]
Option Explicit

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim cCBEvents As clsActiveXEvents
Dim cBtnEvents As clsActiveXEvents
Dim cLblEvents As clsActiveXEvents
Dim cTBEvents As clsActiveXEvents
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then
Set cCBEvents = New clsActiveXEvents
Set cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object
mcolEvents.Add cCBEvents
ElseIf TypeOf shp.OLEFormat.Object.Object Is MSForms.CommandButton Then
Set cBtnEvents = New clsActiveXEvents
Set cBtnEvents.mButtonGroup = shp.OLEFormat.Object.Object
mcolEvents.Add cBtnEvents
ElseIf TypeOf shp.OLEFormat.Object.Object Is MSForms.Label Then
Set cLblEvents = New clsActiveXEvents
Set cLblEvents.mLabelGroup = shp.OLEFormat.Object.Object
mcolEvents.Add cLblEvents
ElseIf TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox Then
Set cTBEvents = New clsActiveXEvents
Set cTBEvents.mTextBoxGroup = shp.OLEFormat.Object.Object
mcolEvents.Add cTBEvents
End If
End If
Next

End Sub
[/vba]
Class (clsActiveXEvents) module

[vba]
Option Explicit

Public WithEvents mCheckboxes As MSForms.CheckBox
Public WithEvents mLabelGroup As MSForms.Label
Public WithEvents mTextBoxGroup As MSForms.TextBox
Public WithEvents mButtonGroup As MSForms.CommandButton

Private Sub mButtonGroup_Click()
MsgBox mButtonGroup.Caption & " has been pressed"
End Sub

Private Sub mLabelGroup_Click()
MsgBox mLabelGroup.Caption & " backcolour is " & mLabelGroup.BackColor
End Sub

Private Sub mTextBoxGroup_Click()
End Sub

Private Sub mTextboxGroup_Change()
MsgBox mTextBoxGroup.Name & " value is is " & mTextBoxGroup.Text
End Sub

Private Sub mCheckboxes_Click()
Dim iCb As Long
If mCheckboxes.Value Then
iCb = Mid(mCheckboxes.Caption, 9, Len(mCheckboxes) + 1)
Worksheets(mCheckboxes.Parent.Name).Range("l" & iCb + 5).Formula = "=K33"
Else
iCb = Mid(mCheckboxes.Caption, 9, Len(mCheckboxes) + 1)
Worksheets(mCheckboxes.Parent.Name).Range("l" & iCb + 5) = 0
End If
End Sub
[/vba]

[JimmyTheHand]

Well, the Class module part is something I'm already familiar with, thanks to your tutoring some time ago. But the sheet module part I'd have never figured out by myself...

One question. Why Worksheet_Activate, why not Workbook_Open instead?

[Bob Phillips]

I thik I did that in case any new controls were added in session. A quick switch of sheets sorts it out.

[janeyjamjar]

Thanks all, I'll give those all a try and see what I come up with!

[stphnlwlsh]

Is there any way to do this MS Access 2007?

[Aflatoon]

This is the Excel forum, but the class approach should work in Access too, I think.