Link VBA Code to Button to transfer data (Run time error)

Hi

I hope someone can help as I am new to VBA.

I need help to link VBA code to a button that will transfer data from a specific row in the "DB" sheet back to the "Input" sheet when you click the "Loan Input" button.

I have the following VBA code in "module3" but I get an error when I link it to the button (I have also attached the workbook) :

Sub RoundedRectangle1_Click()
Application.ScreenUpdating = False
If Target.Column = 1 Then
Sheets("Input Sheet1").Cells(4, 3) = Sheets("DataBase").Cells(Target.Row, 2)
Sheets("Input Sheet1").Cells(6, 3) = Sheets("DataBase").Cells(Target.Row, 3)
Sheets("Input Sheet1").Cells(10, 3) = Sheets("DataBase").Cells(Target.Row, 5)
Sheets("Input Sheet1").Cells(12, 3) = Sheets("DataBase").Cells(Target.Row, 6)
Sheets("DataBase").Rows(Target.Row & ":" & Target.Row).Delete
'Application.Calculate
Sheets("Input").Activate
End If
End Sub

When I do link it to the "Loan Input" button I get an Run time error '424': object required

Thank you in anticipation

If you want to use "Target" as a variable then you need to define it. The best way to do this is by putting into into a sheets VBA and using the Worksheet_SelectionChange() Sub.

If not then you need to use Activecell instead.

Hi ARowbot

Thanks for the reply, I trust the following code what you are talking about?

If so, how do I run this code by linking it to a button?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Sheets("Input").Cells(4, 3) = Sheets("DB").Cells(Target.Row, 2)
Sheets("Input").Cells(6, 3) = Sheets("DB").Cells(Target.Row, 3)
Sheets("Input").Cells(10, 3) = Sheets("DB").Cells(Target.Row, 5)
Sheets("Input").Cells(12, 3) = Sheets("DB").Cells(Target.Row, 6)
Sheets("DB").Rows(Target.Row & ":" & Target.Row).Delete
Application.Calculate
Sheets("Input").Activate
End If

End Sub

That particular code will not be linked to a button. It will run whenever you change a value in the worksheet in column 1 (A). If you want to link it to the button use:

Please Login or Register  to view this content.

To assign this to the button, right click the button and click Assign Macro.

Also, In future make sure your code on the forum is put into code tags like above

Hi LokeshKumar,

Thanks for the quick reply, however your code moves the data from the input sheet to the DB sheet but I need it to move in the reverse (the line selected in the DB sheet table must repopulate the corresponding cells in the Input sheet) when you click the "Load Data" button

I created a module and inserted your code into it and then linked the "load Data" button in the "BD" sheet to the code.

On the "DB" I selected a cell in column A of the table to select the row data that needs to transferred to the input sheet, but when I click on the button to transfer the data I now get a message:
Run-time error '9': Subscript out of range. The error occurs on line "Sheets("Input Sheet1").Cells(4, 3) = Sheets("DataBase").Cells(ActiveCell.Row, 2)"

PS. How do I get the code into code tags like you suggested I just started using the forum a little while back

I noticed when using your spreadsheet that your sheetnames were inconsistent, make sure the sheets are called Input Sheet1 and Database exactly or adjust your code accordingly. You can put them into code tags by pressing the button that looks like #.

I'm so sorry, My bad! I used the code from another workbook I was trying to figure this problem out and forgot to that I changed the sheet names in this workbook.

The code now works like a charm!

Thank you so much for the assistance!