Automatically add button when data added to cell and apply macro to button?

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.

Excellent, Just what I wanted.

One last question...

If I wanted to name the button something othat than 'Button x' within the same code how would I go about it?

Excellent, Thank you.

OK, for some reason the macro has stopped working.

I get the following error

'Run-Time error ‘1004’ – Unable to get the Add property of the Buttons Class'

and the debugger highlights this text in the macro

Please Login or Register  to view this content.

Help very much appreciated.