Only 1 is chosen in a row

[DThib]

Hello,
I am writing an addition to a userform that will pull data to another worksheet from the selected row.
I have several cells in a row that are fed by the result of option buttons, all connected by a frame on the userform. I need to add to a data pull a step that will, once a row is selected by another macro, this set of 7 cells will be searched for the one cell that will not state "N/A".
How can I do that?

 

[DanteAmor]

It can be like this with a global statement

Option Explicit
[COLOR=#0000ff]Dim parts As String[/COLOR]


Sub Prin()


    'still working on
    Dim PL1 As Worksheet, PL2 As Worksheet
    Dim SPR_PL As String
    Dim BPL As Range
    Dim LRPL As Long
    
    SPR_PL = "19-0509" 'FrmCheckIn.SPR_ID.Value"
    
    Set PL1 = Sheets("Checkin")
    Set PL2 = Sheets("LabelQ")
    Set BPL = PL1.Columns("F").Find(SPR_PL, LookIn:=xlValues, LookAt:=xlWhole)
    If Not BPL Is Nothing Then
        'pull information
        LRPL = PL2.Range("C" & Rows.Count).End(xlUp).Row + 1
        PL2.Cells(LRPL, 3).Value = SPR_PL
        
[COLOR=#0000ff]        Call Choosey(PL1, BPL)[/COLOR]
        
        PL2.Cells(LRPL + 2, 3).Value = parts
        PL2.Cells(LRPL + 4, 3).Value = PL1.Cells(BPL.Row, 23).Value
        PL2.Cells(LRPL + 6, 3).Value = PL1.Cells(BPL.Row, 9).Value
        PL2.Cells(LRPL + 8, 3).Value = PL1.Cells(BPL.Row, 8).Value
    Else
        MsgBox "Data does not exist", vbRetryCancel + vbCritical, "FI not identified"
    End If
End Sub


Sub Choosey(PL1, BPL)
    If PL1.Cells(BPL.Row, 10).Value <> "N/A" Then parts = "Console": Exit Sub
    If PL1.Cells(BPL.Row, 11).Value <> "N/A" Then parts = "Bench": Exit Sub
    If PL1.Cells(BPL.Row, 12).Value <> "N/A" Then parts = "Impell": Exit Sub
    If PL1.Cells(BPL.Row, 13).Value <> "N/A" Then parts = "Board": Exit Sub
    If PL1.Cells(BPL.Row, 14).Value <> "N/A" Then parts = "Manager": Exit Sub
    If PL1.Cells(BPL.Row, 15).Value <> "N/A" Then parts = "Keyboard": Exit Sub
    If PL1.Cells(BPL.Row, 16).Value <> "N/A" Then parts = "Assembly": Exit Sub
    If PL1.Cells(BPL.Row, 17).Value <> "N/A" Then parts = "code": Exit Sub
    If PL1.Cells(BPL.Row, 18).Value <> "N/A" Then parts = PL1.Cells(BPL.Row, 18).Value
End Sub

Or with function

Option Explicit


Sub Prin()


    'still working on
    Dim PL1 As Worksheet, PL2 As Worksheet
    Dim SPR_PL As String
    Dim BPL As Range
    Dim LRPL As Long
    
    SPR_PL = "19-0509" 'FrmCheckIn.SPR_ID.Value"
    
    Set PL1 = Sheets("Checkin")
    Set PL2 = Sheets("LabelQ")
    Set BPL = PL1.Columns("F").Find(SPR_PL, LookIn:=xlValues, LookAt:=xlWhole)
    If Not BPL Is Nothing Then
        'pull information
        LRPL = PL2.Range("C" & Rows.Count).End(xlUp).Row + 1
        PL2.Cells(LRPL, 3).Value = SPR_PL
        
        PL2.Cells(LRPL + 2, 3).Value = [COLOR=#0000ff]Choosey(PL1, BPL)[/COLOR]
        PL2.Cells(LRPL + 4, 3).Value = PL1.Cells(BPL.Row, 23).Value
        PL2.Cells(LRPL + 6, 3).Value = PL1.Cells(BPL.Row, 9).Value
        PL2.Cells(LRPL + 8, 3).Value = PL1.Cells(BPL.Row, 8).Value
    Else
        MsgBox "Data does not exist", vbRetryCancel + vbCritical, "FI not identified"
    End If
End Sub


[COLOR=#0000ff]Function Choosey(PL1, BPL)[/COLOR]
    If PL1.Cells(BPL.Row, 10).Value <> "N/A" Then Choosey = "Console": Exit Function
    If PL1.Cells(BPL.Row, 11).Value <> "N/A" Then Choosey = "Bench": Exit Function
    If PL1.Cells(BPL.Row, 12).Value <> "N/A" Then Choosey = "Impell": Exit Function
    If PL1.Cells(BPL.Row, 13).Value <> "N/A" Then Choosey = "Board": Exit Function
    If PL1.Cells(BPL.Row, 14).Value <> "N/A" Then Choosey = "Manager": Exit Function
    If PL1.Cells(BPL.Row, 15).Value <> "N/A" Then Choosey = "Keyboard": Exit Function
    If PL1.Cells(BPL.Row, 16).Value <> "N/A" Then Choosey = "Assembly": Exit Function
    If PL1.Cells(BPL.Row, 17).Value <> "N/A" Then Choosey = "code": Exit Function
    If PL1.Cells(BPL.Row, 18).Value <> "N/A" Then Choosey = PL1.Cells(BPL.Row, 18).Value
End Function

 

[DThib]

All other parts work and place the information where I need. My big problem is how to account for the option of part ID. The interface is a group of option buttons with one text box. Which ever is chosen negates the others.
That is the variable.

 

[DThib]

Thanks, I will try it.

 

[DanteAmor]

Let me know if you have any doubt