Multiple Option Button Click

[johnbird1988]

Hello,

I have a UserForm with 14 different OptionButtons on their names are OptionButton1 to OptionButton14. If any of the buttons are select then it executes a procedure below.

Private Sub OptionButton1_Click()
Call OBC
End Sub
.
.
.
Private Sub OptionButton14_Click()
Call OBC
End Sub

Is there a way instead of having the code repeated 14 time to only reference it the once.

Cheers

John

 

[Domenic]

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

1) In the Visual Basic Editor (Alt+F11), insert a new class module (Insert > Class Module), name it clsButton, and place the following code in the code module for the class...

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]WithEvents[/COLOR] ButtonGroup [COLOR=darkblue]As[/COLOR] MSForms.OptionButton


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ButtonGroup_Click()
    MsgBox ButtonGroup.Caption
    [COLOR=green]'Call OBC[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

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

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Dim[/COLOR] aButtons() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] clsButton

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=darkblue]Dim[/COLOR] oCtrl [COLOR=darkblue]As[/COLOR] Control
    [COLOR=darkblue]Dim[/COLOR] BtnCnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oCtrl [COLOR=darkblue]In[/COLOR] Me.Controls
        [COLOR=darkblue]If[/COLOR] TypeName(oCtrl) = "OptionButton" [COLOR=darkblue]Then[/COLOR]
            BtnCnt = BtnCnt + 1
            [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aButtons(1 [COLOR=darkblue]To[/COLOR] BtnCnt)
            [COLOR=darkblue]Set[/COLOR] aButtons(BtnCnt).ButtonGroup = 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!

 

[AddinBox_Tsunoda]

Hi.


You can describe an event handling of plural control by one procedure in the UserForm module
like VB (not VBA) to introduce even the following thread by using two class modules.


http://www.mrexcel.com/forum/excel-questions/820733-one-code-multiple-userform-control-changes.html


http://www.mrexcel.com/forum/excel-...-one-multiple-check-box-controls-clicked.html




You can program it as follows when you use clsBpca (See below for further details).

-- UserForm module --
Private WithEvents OptButtons As clsBpca




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




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




' All click events of OptionButton1-OptionButton14 
' are handled by this one procedure.
Private Sub OptButtons_Click(ByVal Index As Integer)
  'Call OBC


  If (OptButtons.Item(Index).Value = True) Then
     'ON processing
     MsgBox OptButtons.Item(Index).Name & " [ON]"
  Else
     'OFF processing
     MsgBox OptButtons.Item(Index).Name & " [OFF]"
  End If
End Sub

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 )



[ Implementation of the event handling by API : ConnectToConnectionPoint ]
In the class module of VBA, it cannot usually receive events of Enter , Exit ,
BeforeUpdate and AfterUpdate of the MsForms-Controls.
It become able to receive the events by using API.
AddinBox( Breakthrough in the Pseudo Control Array : clsBpca Reference )