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





"Patrick Molloy" wrote:

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

"Ayo" wrote:

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