I've got this code which adds a button into the next cell across from where text is entered, i.e if I enter text in A5 the button appears in B5. This Code is entered in workbook 2

Public Sub Worksheet_Change(ByVal Target As Range)
Dim col As Integer
Dim row As Integer
col = Target.Column
row = Target.row
If Not IsNull(Target.Value) And Not IsEmpty(Target.Value) Then
Application.EnableEvents = False
Buttons.Add Cells(row, col + 1).Left, Cells(row, col + 1).Top, Cells
(row, col + 1).Width, Cells(row, col + 1).Height
Application.EnableEvents = True
End If
End Sub


I want these buttons to automatically have a macro assigned to them so that it copies the text from the cell prior to the one they are in, i.e. the button is in cell B5 and the text is in A5, and then pastes it to another worksheet (In workbook 1).

Currently I assign the macro to buttons manually. This is the macro I use.

Sub Macro2()
'Application.ScreenUpdating = False
Text = Range("A5")
Windows("Workbook 1.xlsm").Activate
Dim i As Range
Range(Range("C4"), Range("C4")).Select
For Each i In Selection
i.Value = i.Value & Text & " "
Next i
Windows("Workbook 2.xlsm").Activate
'Application.ScreenUpdating = True
End Sub

The range A5 represents where the text is copied from and this changes depending on which text I want to copy.

Range C4 is where I'm posting to in Workbook 1 and this remains constant.


Can anyone advise what changes I need to make to the macro so that it always takes the text from the cell prior to where the button is located, rather than having to have a seperate macro for every cell?

Also, I've been told I could use function Selection.OnAction = . I've done a bit of reading but I just don't understand it?

Thanks very much in advance.