Loop through every 3rd OptionButton on a Userform

penfold23

New Member
Joined
Jan 28, 2014
Messages
2


<o:p>
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:p></o:p>

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:p></o:p>


<o:p></o:p>
Code:
[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:p>

 
Last edited:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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).

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top