Detect which option button on form is clicked

[Srbin do jaja]

I have a userform and around 400 option buttons in groups of 5 that are inside of frame (its a survey). Now i want to change background of each option button that is clicked. That means if optionbutton's value = true then optionbutton's background lets say becomes red.

 

[Srbin do jaja]

here is what i'm trying to do

Private Sub OptionButton1_AfterUpdate()
    OptionButton1.BackColor = RGB(255, 255, 0)
End Sub

Private Sub OptionButton2_AfterUpdate()
    OptionButton2.BackColor = RGB(255, 255, 0)
End Sub

Private Sub OptionButton3_AfterUpdate()
    OptionButton3.BackColor = RGB(255, 255, 0)
End Sub

but i dont want to write 400 of these routines. is there a better way?

 

[wigi]

This is possible, yes. But the code for that is rather advanced: you need to use classes to handle controls and there events (like clicking a control such as an option button).

I would also think that you can have the same effects with "only" 40 optionbuttons or 20. Using a multipage control on a userform, you present each time 8 questions in the survey. Clicking "next page" (a command button that activates the next page) will show different questions. But in reality it's all the same controls (labels, option buttons, ...) that are cleared and presented again to the user.

 

[Srbin do jaja]

This is possible, yes. But the code for that is rather advanced: you need to use classes to handle controls and there events (like clicking a control such as an option button).

I would also think that you can have the same effects with "only" 40 optionbuttons or 20. Using a multipage control on a userform, you present each time 8 questions in the survey. Clicking "next page" (a command button that activates the next page) will show different questions. But in reality it's all the same controls (labels, option buttons, ...) that are cleared and presented again to the user.

well i have 90 questions split in 9 multipages, i do have buttons to navigate trough multipages. and i do need that advance code, since i aint that good at classes. dont know how to detect events inside of class.

 

[wigi]

You will have a number of things to be set up:

1.

Call a procedure like GetOptionButtons in one of the Userform's events: for instance Userform_Initialize()

2.

That procedure (in a regular module) contains code like this:

Dim OB() As New OBclass

Sub GetOptionButtons()
    
    ReDim OB(1 To 3)
    For i = 1 To 3 '400
        Set OB(i).OBgroup = UserForm1.Controls("OptionButton" & i)
    Next

End Sub

3.

Add a Class module called OBclass. This class will contain following code:

Option Explicit

Public WithEvents OBgroup As MSForms.OptionButton

Private Sub OBgroup_Click()

    OBgroup.BackColor = vbRed 'RGB(255, 255, 0)

End Sub

That's it.

 

[Srbin do jaja]

its not working. it shows 424 error.

 

[wigi]

This code above is all copy-pasted from an example I set up, which worked fine for me.

 

[MickG]

Just to confirm wigi's code worked for me !!!!
I places the Sub GetOptionButtons() in the Userform1 initialize code as below, which help me.
If you have "Option Explicit" set as below you will need to also Dimension the variable "i", as shown

Option Explicit
Dim OB() As New OBclass
Private Sub UserForm_Initialize()
Dim i As Integer
'Sub GetOptionButtons()
    ReDim OB(1 To 8)
    For i = 1 To 8 '400
            Set OB(i).OBgroup = UserForm1.Controls("OptionButton" & i)
    Next
End Sub

The Class Procedure below must be placed in a Class Module (Not ordinary module)
When you Create a Class module as in this case it will show in the properties window of the VB code window as "Class1".
If you highlight it's name, then click "F4", its properties window will show , and you Must change its name to "OBclass"

Option Explicit
Public WithEvents OBgroup As MSForms.OptionButton
Private Sub OBgroup_Click()
      OBgroup.BackColor = vbRed 'RGB(255, 255, 0)
End Sub

Acknowledgement to wigi. !!
Regards Mick

 

[Jaafar Tribak]

Here is an alternative Method that should work for all OptionButtons without the need for a Class Module. Still they all use a generic common event Procedure.

Place this in the UserForm Module and you will see that all the Option Buttons will turn green when clicking on them.

Option Explicit

Private WithEvents Ws As Worksheet

Private Sub UserForm_Initialize()

    Dim i As Long
    Dim oCtl As Control
    
    Application.EnableEvents = False
    Set Ws = ThisWorkbook.Worksheets.Add
    Ws.Visible = xlSheetHidden
    For Each oCtl In Controls
        If TypeName(oCtl) = "OptionButton" Then
            i = i + 1
            oCtl.ControlSource = Ws.Cells(i, 1).Address(, , , True)
            oCtl.Value = False
        End If
    Next
    Application.EnableEvents = True

End Sub

Private Sub Ws_Change(ByVal Target As Range)

    Dim oCtl As Control
    
    For Each oCtl In Controls
        If TypeName(oCtl) = "OptionButton" Then
            oCtl.BackColor = vbButtonFace
            oCtl.Value = False
        End If
    Next
    ActiveControl.BackColor = vbGreen
    ActiveControl.Value = True

End Sub


Private Sub UserForm_Terminate()

    With Application
        .DisplayAlerts = False
        Ws.Delete
        .DisplayAlerts = True
        .EnableEvents = True
    End With

End Sub

 

[wigi]

Mick: thanks for expanding, and Jaafar Tribak: interesting approach !