Help with Option Buttons and Frame

[Ayo]

I have a Frame with about 10 Optionbuttons. I am trying to figure out a way
such that when I click on any of the option buttons, something is done. I
can't figure out how to do this without having to write 10 of this:

Private Sub OptionButton1_Click()

End Sub

Is there a way to do it all in one procedure instead of 10?

Thanks

 

[JLGWhiz]

Each control has its own events because it is a separate object. If you
want to perform an action based on a click event of the control, the code
must be tied to the control performing the event. So the short answer is
you have to write code for each control.

 

[B Lynn B]

I don't believe there is a way around having code for each button's event.
However, you can write a separate sub procedure for the one block of code you
want them all to execute the same, then have each button's event just call
that sub instead of repeating it 10 times.

 

[Dave Peterson]

John Walkenbach explains how to do this using a class module:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure/

You'll want to insert a class module in the workbook's project
(Insert|class module in the VBE).

It'll be named Class1 (unless you rename it or add more).

I used Class1 in this example.

This code goes into the class module:

Option Explicit
Public WithEvents OptBtnBoxGroup As MSForms.OptionButton
Private Sub OptBtnBoxGroup_Change()
MsgBox OptBtnBoxGroup.Name & " changed" & vbLf & OptBtnBoxGroup.GroupName
End Sub

(You may want to give nice groupnames to each of the optionbuttons to make life
simpler.)


Then inside the userform module:

Option Explicit
Dim OptBtnBoxes() As New Class1
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim ctl As Control
Dim OptBtnBoxCtr As Long

OptBtnBoxCtr = 0
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.OptionButton Then
OptBtnBoxCtr = OptBtnBoxCtr + 1
ReDim Preserve OptBtnBoxes(1 To OptBtnBoxCtr)
Set OptBtnBoxes(OptBtnBoxCtr).OptBtnBoxGroup = ctl
End If
Next ctl

End Sub

Then show the userform and test it out.

 

[Ayo]

Thanks

I don't believe there is a way around having code for each button's event.
However, you can write a separate sub procedure for the one block of code you
want them all to execute the same, then have each button's event just call
that sub instead of repeating it 10 times.

 

[Patrick Molloy]

yes this can be done . Are you using a userform or activeX controls on a
worksheet?

 

[Patrick Molloy]

Userform method:
there are three parts to this. We need a collection to hold the controls, an
object to represent the controls and an event handler to respond...

(1) To the VBAProject ad a CLASS MODULE, rename it clsControl. This name is
used later, so be careful and make sure Option Explicit is on each module
Add this code:

Option Explicit
Private WithEvents m_Control As MSForms.OptionButton
Public Event Changed(ctrl As Control)
Property Set Control(newControl As MSForms.Control)
Set m_Control = newControl
End Property
Private Sub m_Control_Click()
UserForm1.Changed m_Control
End Sub

(2) add a userform. I assume the default name to be Userform1 later. Drop
any number of option buttons onto it. Leave the default name as "Option" as
we use this in the code. Paste this code:
Option Explicit
Dim colControls As New Collection
Dim ctControl As clsControl
Sub Changed(ctrl As Control)
MsgBox ctrl.Name & " :" & ctrl.Value
End Sub
Private Sub UserForm_Initialize()
Dim ctrl As Control
For Each ctrl In Controls
If ctrl.Name Like "Option*" Then
'AddValues ctrl 'for demo
Set ctControl = New clsControl
Set ctControl.Control = ctrl
colControls.Add ctControl
End If
Next
End Sub


(3) fuinanlly, but not necessary, add some code in a standard MODULE to
launch the userform
Sub ShowForm()
Dim uf As UserForm1
Set uf = New UserForm1
uf.Show
End Sub


So the userform code loops through the controls. Any "option" buttons are
set to the clsControl object and saved in the collection. when a control is
clicked, the clsControl event call the userform's Changed procedure and you
see the message box.

Its extremely easy to adapt for ANY kind of control

click YES if this was helpful