Need help looping through groups of option buttons

Question

I have an Excel "Sheet1" with option buttons and 2 control buttons (OK, Clear)

The object of this Sheet is to learn some coding with controls.

D6:D14 (contents to be copied)

P6:P14 (if option button1 is true, then paste here)

Q6:Q14 (if option button2 is true, then paste here)

The same needs to be repeated or looped for the rest of the rows.

This is the code for row 6, option buttons are paired as optionbutton1&2, 3&4, 5&6, etc...

Private Sub CommandButton1_Click()

If Sheet1.OptionButton1.Value = True Then
  Range("P6").Select
  ActiveCell.FormulaR1C1 = "=RC[-12]"
  Range("P6").Select
  Range("Q6").Clear

Else

  Range("Q6").Select
  ActiveCell.FormulaR1C1 = "=RC[-13]"
  Range("Q6").Select
  Range("P6").Clear

End If

Answer 1

This approach isn't very flexible but makes the code a little easier.

Name each option button with an underscore and number like this: optionButton_1

Name each option button group the same way, but with the number being the row that the cell is on. For example, option buttons related to cell D6 could be group btnGroup_6

Now you can loop through each option button and use the button and group names to easily determine destination like this:

Private Sub btn_OK_Click()
    Dim oOption As OLEObject
    For Each oOption In Sheet1.OLEObjects
        If oOption.OLEType = 2 Then
            If oOption.Object = True Then
                GroupNumber = Split(oOption.Object.GroupName, "_")(1)
                ButtonNumber = Split(oOption.Name, "_")(1)

                'Test is button is odd or even to determine destination
                If ButtonNumber Mod 2 Then
                    Range("D" & GroupNumber).Copy Destination:=Range("P" & GroupNumber)
                Else
                    Range("D" & GroupNumber).Copy Destination:=Range("Q" & GroupNumber)
                End If
            End If
        End If
    Next oOption
End Sub

---

Test Results:

---