Populating a textbox with results from multiple option buttons

[ryeire]

Hi all,

I'm currently creating a database for doing checks and logging wether a station has passed or failed the check.

I have a userform with a pass/fail option button for each station and I'm looking to populate a textbox in another userform with the station numbers that have failed.
However, I am struggling to find a way to populate one textbox with results from multiple option button groups. I have tried elseif statements and a number of techniques but I have had no luck and my excel vba knowledge is still limited.

So you select what stations have passed/failed then when you press continue the second userform appears. I would like the failed stations to be populated automatically into the Failed Station Numbers text box. I have it coded so that the date and area automatically populated.

This is an example of the code I was trying:

Private Sub cmdSTcheck_Click()

DiodeForm.Hide

frmWeeklyForm.txtArea.Value = "Diode"

If Me.opt1fail.Value = True Then
frmWeeklyForm.txtFails.Value = "1"
End If

If Me.opt2fail.Value = True Then
frmWeeklyForm.txtFails.Value = "2"
End If

frmWeeklyForm.Show

End Sub

 

[dmt32]

Hi,
untested but assuming your naming convention for the "Fail" optionbuttons remains consistent then try following & see if does what you want

Private Sub cmdSTcheck_Click()
    Dim Fails   As String
    Dim i       As Long
    
    For i = 1 To 42
        With Me.Controls("Opt" & i & "Fail")
            If .Value Then Fails = Fails & i & ","
        End With
    Next i
    
    Fails = Mid(Fails, 1, Len(Fails) - 1)
    frmWeeklyForm.txtFails.Value = Fails
End Sub

Dave

 

[ryeire]

@dmt32 it worked perfectly first time! Thanks a million

 

[dmt32]

@dmt32 it worked perfectly first time! Thanks a million

I amaze myself sometimes but glad suggestion does what you want & appreciate your feedback

Dave

 

[ryeire]

@dmt32 I now have one small problem with the code. If all of the stations pass and I submit I get an error within the code. Do you know of a way to fix this?

 

[dmt32]

Try

Private Sub cmdSTcheck_Click()
    Dim Fails   As String
    Dim i       As Long
    
    For i = 1 To 42
        If Me.Controls("Opt" & i & "Fail").Value Then _
        Fails = Fails & i & ","
    Next i
    
    If Len(Fails) > 0 Then
        Fails = Mid(Fails, 1, Len(Fails) - 1)
        frmWeeklyForm.txtFails.Value = Fails
    End If
End Sub

Note: When posting code, use code tags from then menu bar - Press VBA which will produce the tags & place you code between them

Dave

 

[ryeire]

Dave you really are amazing! Worked perfectly again thank you very much.

Sorry about the code I just wanted to show where the error was - which is why I used a screenshot

 

[dmt32]

most welcome - appreciate the feedback

Dave