Simplifying numerous checkbox procedures into 1 procedure

[PJ Murph]

I have 400 checkboxes (beginning with cbCheck1 thru cbCheck400). How do I
write code that doesn't have to repeat this procedure 400 times?

Private Sub cbCheck1_Click()
Select Case cbCheck1.Value
Case True: LblOnHold1.Visible = True: LblOnHold1.PrintObject = True
Case False: LblOnHold1.Visible = False: LblOnHold1.PrintObject = False
End Select
End Sub

 

[Dave Peterson]

First, you could drop the select case stuff and just use the cbCheck1.value
LblOnHold1.Visible = cbcheck1.value
LblOnHold1.PrintObject = cbcheck1.value

Second, the labels are also named LblOnHold1 through LblOnHold400?

If yes...

You'll want to look at John Walkenbach's site:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure

And finally, these are on a worksheet, right?

If yes to all that stuff, then
This code goes into a General module:

Option Explicit
Dim ChkBoxes() As New Class1
Sub Autpen()

Dim CBXCount As Long
Dim OLEObj As OLEObject

CBXCount = 0
For Each OLEObj In Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
If LCase(OLEObj.Name) = LCase("cbCheck") Then
CBXCount = CBXCount + 1
ReDim Preserve ChkBoxes(1 To CBXCount)
Set ChkBoxes(CBXCount).CBXGroup = OLEObj.Object
End If
End If
Next OLEObj

End Sub


And then when you're in the VBE, do Insert|Class Module
The name of this class module is Class1 (it's important to match what's in the
code):

Then paste this in the newly opened code window:

Public WithEvents CBXGroup As MSForms.CheckBox
Private Sub CBXGroup_Change()
Dim WhichOne As Long

WhichOne = Mid(CBXGroup.Name, Len("cbCheck") + 1)

With CBXGroup.Parent.OLEObjects("LblOnHold" & WhichOne)
.Visible = CBXGroup.Value
.PrintObject = CBXGroup.Value
End With

End Sub

Just an aside...

From a user's point of view, 400 checkboxes are an awful lot!

 

[PJ Murph]

First, you could drop the select case stuff and just use the cbCheck1.value
LblOnHold1.Visible = cbcheck1.value
LblOnHold1.PrintObject = cbcheck1.value

Second, the labels are also named LblOnHold1 through LblOnHold400?

If yes...

You'll want to look at John Walkenbach's site:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure

And finally, these are on a worksheet, right?

If yes to all that stuff, then
This code goes into a General module:

Option Explicit
Dim ChkBoxes() As New Class1
Sub Autpen()

Dim CBXCount As Long
Dim OLEObj As OLEObject

CBXCount = 0
For Each OLEObj In Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
If LCase(OLEObj.Name) = LCase("cbCheck") Then
CBXCount = CBXCount + 1
ReDim Preserve ChkBoxes(1 To CBXCount)
Set ChkBoxes(CBXCount).CBXGroup = OLEObj.Object
End If
End If
Next OLEObj

End Sub


And then when you're in the VBE, do Insert|Class Module
The name of this class module is Class1 (it's important to match what's in the
code):

Then paste this in the newly opened code window:

Public WithEvents CBXGroup As MSForms.CheckBox
Private Sub CBXGroup_Change()
Dim WhichOne As Long

WhichOne = Mid(CBXGroup.Name, Len("cbCheck") + 1)

With CBXGroup.Parent.OLEObjects("LblOnHold" & WhichOne)
.Visible = CBXGroup.Value
.PrintObject = CBXGroup.Value
End With

End Sub

Just an aside...

From a user's point of view, 400 checkboxes are an awful lot!

Dave,
This is a work of art...beautiful! Thank you so much!