Clear all option button backcolor on UserForm

[jeffreybrown]

I have a Userform which when open on UserForm_Initialize will set the backcolor of OptionButton16 t vbYellow. When the user selects one of the other 15 OptionButtons, I would like that OptionButton to have a Backcolor of vbYellow, but the previous OptionButton is cleared of backcolor. Basically only on optionbutton clicked at a time with the backcolor.

 

[DanteAmor]

Try this:

In your userform, Initialize event:

Dim OptBt() As New Class1         'At the start of all code

Private Sub UserForm_Initialize()
  Dim i As Long, ctrl As MSForms.Control
  i = 1
  
  OptionButton16.BackColor = vbYellow
  
  For Each ctrl In Me.Controls
    If TypeName(ctrl) = "OptionButton" Then
      ReDim Preserve OptBt(i)
      Set OptBt(i).MultOption = ctrl
      i = i + 1
    End If
  Next
End Sub

In a class module. Call "Class1"
Note: Change "Userform1" By the name of your userform

Option Explicit

Public WithEvents MultOption As MSForms.OptionButton

Private Sub MultOption_Change()
  Dim ctrl As MSForms.Control
  
  For Each ctrl In UserForm1.Controls         'fit to the name of your userform
    If TypeName(ctrl) = "OptionButton" Then
      ctrl.BackColor = &H8000000F
    End If
  Next
  MultOption.BackColor = vbYellow
End Sub

 

[jeffreybrown]

Thanks Dante. This works great for my needs but now I'd like to drop the caption of the optionbutton click into the activecell of my spreadsheet.

I've been trying this, but it debugs at the If statement.

Private Sub CommandButton1_Click()
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "OptionButton" And Ctrl.Value = True Then
            MsgBox Ctrl.Caption
            ActiveCell = Ctrl.Caption
        End If
    Next
End Sub

 

[DanteAmor]

Try

Private Sub CommandButton1_Click()
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "OptionButton" then
If Ctrl.Value = True Then
            MsgBox Ctrl.Caption
            ActiveCell = Ctrl.Caption
End if
        End If
    Next
End Sub

 

[jeffreybrown]

That works great. Thank you.