Return Label caption and corresponding option button caption as list

Question

I'm using an Excel VBA UserForm where questions are dynamically displayed as label captions and all of the displayed questions have a Yes/No option button group next to them.

I'm trying to return the captions of the labels and the selected answer, but excel seems to be randomly choosing to go through the option buttons.

What I have is:

• Question 1 - Answer 3
• Question 2 - Answer 1
• Question 3 - Answer 2

What I want is a text output that I can then copy into an email, something like this:

• Question 1 - Answer 1
• Question 2 - Answer 2
• Question 3 - Answer 3

The weird thing is, sometimes the order of the last questions are swapped. (I even changed the tab index, but that didn't help).

What am I missing here?

Thanks for your help.

The Labels are called Label1, Label2, Label3, etc. The option buttons are called Optbtn1y, Optbtn1n, Optbtn2y, Optbtn2n, Optbtn3y, Optbtn3n, etc.

Private Sub returnoptbttnv()
 
'determine name and caption of all enabled OptionButtons in a Frame
 
Dim ctrl As Control
Dim Lbel As Control
Dim txt1 As String
Dim txt2 As String
txt1 = ""
txt2 = ""
 
For Each Lbel In Frame2.Controls
    If TypeName(Lbel) = "Label" And Lbel.Visible = True Then
    txt1 = Lbel.Caption
    End If
Next Lbel
    
For Each ctrl In Frame2.Controls
    If TypeOf ctrl Is MSForms.OptionButton Then
    If ctrl.Value = True And ctrl.Visible = True Then
    txt2 = ctrl.Caption
    End If
    End If
Next ctrl
    MsgBox (txt1 & txt2)
End Sub

Answer 1

It looks like one your requirements is to return a list of all questions and answers. I took a different approach by not looping through the controls collection:

Private Function GetAnswers() As String
   On Error Resume Next

   Dim i As Integer
   Dim question As MSForms.Label
   Dim answer As MSForms.OptionButton
   
   GetAnswers = ""
   
   i = 0
   
   Do
      i = i + 1
      Set question = Nothing
      Set question = Me.Frame2.Controls("Label" & i)
      
      If question Is Nothing Then Exit Do
      
      If question.Visible Then
         Set answer = Me.Frame2.Controls("Optbtn" & i & "Y")
         If Not answer.Value Then Set answer = Me.Frame2.Controls("Optbtn" & i & "N")
         GetAnswers = GetAnswers & question.Caption & " - " & answer.Caption & vbCrLf
      End If
   Loop
End Function