Private Sub to activate if any one of multiple check box controls are clicked

[DanMee]

Hi All,

I have some code that applies to 24 different check boxes (named inventively, CheckBox1 through to CheckBox24).
Currently I have a 'Private Sub CheckboxX_Click()' for each named check box, so 24 sets of identical code.

Is there a way, purely to tidy up my coding, to get this code to apply to any check box from 1 to 24 that is clicked?

Does that make sense?

Kind regards,

Dan

 

[mole999]

maybe worksheet change event

 

[Domenic]

You'll need to use a class module to create a new class object...

1) Insert a class module (Insert > Class Module), name it clsChBxGroup, and enter the following code...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]WithEvents[/COLOR] CheckBoxGroup [COLOR=darkblue]As[/COLOR] msforms.CheckBox

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBoxGroup_Click()
    MsgBox CheckBoxGroup.Name
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

2) In the code module for the UserForm, enter the following code...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Dim[/COLOR] ChBxs() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] clsChBxGroup

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=darkblue]Dim[/COLOR] oCtrl [COLOR=darkblue]As[/COLOR] Control
    [COLOR=darkblue]Dim[/COLOR] lCnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    lCnt = 0
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oCtrl [COLOR=darkblue]In[/COLOR] Me.Controls
        [COLOR=darkblue]If[/COLOR] TypeName(oCtrl) = "CheckBox" [COLOR=darkblue]Then[/COLOR]
            lCnt = lCnt + 1
            [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] ChBxs(1 [COLOR=darkblue]To[/COLOR] lCnt)
            [COLOR=darkblue]Set[/COLOR] ChBxs(lCnt).CheckBoxGroup = oCtrl
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] oCtrl
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[DanMee]

Hi Dom,

Thanks for the help! Just one question, where do I put the code I want to execute when a checkbox is clicked? Does it go in the class module between the Sub tags?

 

[Sektor]

This works only for ActiveX controls.
For ActiveX - they automatically go to worksheet code module (when you double-click it).

 

[Domenic]

For the first code, insert a class module (Alt+F11 > Insert > Class Module), and copy/paste the code in the code module. For the second code, copy paste the code in the code module for the UserForm (right-click the UserForm, and select 'View Code').

Does this help?

 

[AddinBox_Tsunoda]

Hi, all.


You can define all click events of CheckBox1-CheckBox24 by one procedure
in the UserForm module when you use two class modules. (Event coding of
Control-Array which is completely equal to VisualBasic is possible.)


Two class modules (clsBpca & clsBpcaCh) are general-purpose designs.
It is not necessary to modify the class modules.
You only import the class modules and can use it for every controls.


Please look at the following figure and the site about the structure.
(Because I do not protect the macro, you can watch the macro of the class modules.)
[ Illustration of clsBpca Concept ]
http://www.h3.dion.ne.jp/~sakatsu/GifImg/GifDoc/Bpca_Concept.gif
[ clsBpca document ]
AddinBox( Breakthrough in the Pseudo Control Array )
[ Download & Reference guide ]
AddinBox( Breakthrough in the Pseudo Control Array : clsBpca Reference )




You can program it as follows when you use clsBpca.

-- UserForm module --
Private WithEvents ChkBoxes As clsBpca


Private Sub UserForm_Initialize()
Dim j As Integer
  Set ChkBoxes = New clsBpca
  With ChkBoxes
    For j = 1 To 24
      .Add Me.Controls("CheckBox" & j)
    Next j
    .Rgst  BPCA_Click
  End With
End Sub


Private Sub UserForm_Terminate()
  ChkBoxes.Clear
  Set ChkBoxes = Nothing
End Sub


' All click events of CheckBox1-CheckBox24 
' are handled by this one procedure.
Private Sub ChkBoxes_Click(ByVal Index As Integer)
  If (ChkBoxes.Item(Index).Value = True) Then
     'ON processing
     MsgBox ChkBoxes.Item(Index).Name & " [ON]"
  Else
     'OFF processing
     MsgBox ChkBoxes.Item(Index).Name & " [OFF]"
  End If
End Sub