Loop through every 3rd OptionButton on a Userform

[penfold23]

<o>
Hi, I have an excel (2013) userform which contains a multipage. There are 4 pages on this multipage and spread across these pages are 26 frames eachcontaining 3 option buttons (values Yes, No and N/A) - basically, the answers to 26 questions. If any of the questions are answered 'No', I would like to automatically change the value of a final Frame containing 2 optionbuttons (Yes and No) to 'No'.<o></o>

I have written various versions of the following code but I either get a message telling me that it cannot find the Control or a Type Mismatch. Is anyone able to tell mewhere I am going wrong please?<o></o>


<o></o>

[SIZE=3]Private SubcmdSubmit()[/SIZE]
[SIZE=3]Dim LastRow As Object<o:p></o:p>[/SIZE]
[SIZE=3]Dim i As Long<o:p></o:p>[/SIZE]
[SIZE=3]Dim pg As msforms.Page<o:p></o:p>[/SIZE]

[SIZE=3]Set LastRow =Sheets("Data").Range("A65536").End(xlUp)<o:p></o:p>[/SIZE]

[SIZE=3]For Each pg In Me.MultiPage1.Pages<o:p></o:p>[/SIZE]
[SIZE=3]  For i = 2 To 77 Step 3<o:p></o:p>[/SIZE]
[SIZE=3]    If Me.MultiPage1.Pages(pg).Controls("OptionButton" & i).Object.Value= True Then<o:p></o:p>[/SIZE]
[SIZE=3]       Me.OptionButton92 = True<o:p></o:p>[/SIZE]
[SIZE=3]       LastRow.Offset(1, 33).Value ="No"<o:p></o:p>[/SIZE]
[SIZE=3]       Else: Me.OptionButton91 = True<o:p></o:p>[/SIZE]
[SIZE=3]             LastRow.Offset(1, 33).Value ="Yes"<o:p></o:p>[/SIZE]
[SIZE=3]    End If<o:p></o:p>[/SIZE]
[SIZE=3]  Next i<o:p></o:p>[/SIZE]
[SIZE=3]Next pg<o:p></o:p>[/SIZE]
[SIZE=3]End Sub<o:p></o:p>[/SIZE]

</o>

 

[JamesW]

Hi,

Welcome to the board.

Does this help?

Rather than trying to use i and stepping, it loops through each option button where the caption = "No". If this is set as true then it sets a boolean variable to True.
After all the loops it says "if the boolean is true then I must have found a "No" value, therefore set my variables as true.

I've also changed your LastRow set (and declared it as a Range rather than Object).

Private SubcmdSubmit()
    Dim LastRow As Range
    Dim pg As MSForms.Page
    Dim cnt As MSForms.Control
    Dim bNoFound As Boolean

    Set LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp)

    For Each pg In Me.MultiPage1.Pages
        For Each cnt In pg.Controls
            If TypeOf cnt Is MSForms.OptionButton Then
                If cnt.Caption = "No" And cnt.Value = True Then
                    bNoFound = True
                End If
            End If
        Next cnt
    Next pg

    If bNoFound = True Then
        Me.OptionButton91.Value = True
        LastRow.Offset(1, 33).Value = "Yes"
    Else
        Me.OptionButton91.Value = False
        LastRow.Offset(1, 33).Value = "No"
    End If
        

    Set LastRow = Nothing
    Set pg = Nothing
    Set cnt = Nothing
    bNoFound = False
End Sub

 

[penfold23]

Thanks James, that worked brilliantly. Much appreciated