Copying Option Button 3 and Option Button 2 into excel sheet column C

mal_ivan · Oct 1st 2017

I use this code below to transfer to my excel sheet

Code

Sub TransferToColB()
    Sheets("Sheet1").Select




    For i = 2 To 1000000
        If Cells(i, 1).Value = "" Then
            If UserFormEntry.TextBox11.Value = "" Then
                MsgBox "No Data Populated", vbCritical, "Error Message"
                GoTo EndofTransfer
            Else
                If UserFormEntry.TextBox1.Value = "" Then
                    Cells(i, 1).Value = " N/A"
                Else
                    Cells(i, 1).Value = UserFormEntry.TextBox1.Value
                End If
                Cells(i, 2).Value = UserFormEntry.TextBox11.Value
                GoTo EndofTransfer
            End If
        End If
    Next i
EndofTransfer:
End Sub

Display More

Now I created column C in excel and would like Inbound and Outbound option button to be copied to Column C in excel.

Below is my code for inbound and outbound

Code

For i = 2 To 1000000
        If Cells(i, 1).Value = "" Then
               'Note
                'Calltype
                If UserFormEntry.OptionButton3.Value = True Then
                    InOut = "Inbound"
                ElseIf UserFormEntry.OptionButton2.Value = True Then
                    InOut = "Outbound"
                Else:
                    InOut = "N/A"
                End If

Display More

The attached is my excel sheet and userentryform.
Please share with me on how I can copy my inbound and outbound option button to Column C in excel.

**mikerickson** · Oct 1st 2017

Re: Copying Option Button 3 and Option Button 2 into excel sheet column C

I think this code will do what you want

Code

With ActiveSheet


    With .Names
        If Me.OptionButton3.Value Then
            .Add Name:="OptionButtonString", RefersTo:="=""InBound"""
        ElseIf Me.OptionButton2.Value Then
            .Add Name:="OptionButtonString", RefersTo:="=""OutBound"""
        Else
            .Add Name:="OptionButtonString", RefersTo:="=""odd"""
        End If
    End With
    
    With Range("C2:C1000000")
        .FormulaR1C1 = "=IF(RC1="""", ""N/A"",OptionButtonString)"
        .Value = .Value
    End With
    
    .Names("OptionButtonString").Delete
End With

Display More

Copying Option Button 3 and Option Button 2 into excel sheet column C

Participate now!

Share