Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy amount to specific column based on selected optionbutto



I  need fix error copying amount  from textbox to  specific  column based on selected one of  the  optionbuttons .

if  I  select  optionbutton1 and  fill amount  in textbox 63 , then  should  copy  to  column E and  if  i select optionbutton2 and  fill amount in textbox63 then  should  copy  to  column D . currently  the  code  will copy  to  column E for  both optionbuttons




Selected Answer


You were nearly there in setting vairaible myCol. In the attached, revised file you'll see that you just needed to replace the line:

        .Range("E" & lr + 1) = TextBox63.Value

with the second one in bold below (the first bold line is new and gets to the user to pick an option):

Private Sub CommandButton7_Click()

Dim ws As Worksheet
Dim myCol$
Dim lr As Long

' check an option was picked
If OptionButton7.Value = False And OptionButton8.Value = False Then MsgBox "Please pick option PAID or RECEIVED"

If OptionButton7.Value = True Then
    Set ws = Sheets("voucher")
    myCol = "E"
    ElseIf OptionButton8.Value = True Then
    Set ws = Sheets("voucher")
    myCol = "D"
End If

If Not ws Is Nothing And Len(myCol) Then
    With ws
        ' determine last used row
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        ' write to free row
        .Range("A" & lr + 1) = Label63.Caption
        .Range("B" & lr + 1) = TextBox61.Value
        .Range("C" & lr + 1) = TextBox62.Value
        ' put value in correct column
        .Range(myCol & lr + 1) = TextBox63.Value
    End With
End If

End Sub

Note that I added a new green button green button (labelled "Launch form") which has this code assigned to it:

Sub RectangleRoundedCorners2_Click()


End Sub

Click it and your UserForm will appear.

Minor thing- I corrected your spelling of RECEIVED too.

Hope this helps.



Hi John,
much apprecited  for  your support and  assistance .
thank  you .
Alaa (rep: 30) May 22, '23 at 12:59 pm
Glad that helped. Thanks for selecting my Answer, Alaa. 
John_Ru (rep: 6232) May 22, '23 at 1:12 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login