How to bind VBA macro to specific cell?

Question

I'm working on an excel sheet where I have data organized in rows and need a way to run a VBA macro which will execute program based on data found in specific row.

What I'm struggling to achieve is to find a mechanism for users to simply click something button-like positioned after data to complete this task. Also users should be able to copy an existing row (and macro trigger with it) and execute macro related to that new row without having to do any code modifications.

What I have tried so far is excel button but it has no way of binding to certain cell. Also macro has to be assigned manually every time new row is added. I also tried 'Worksheet_SelectionChange' event with filtering to certain column containing link-like text. This works fine in a way, but some users use arrow keys to navigate within sheet which accidentally triggers macros if macro cell gets selected. Also, mouse cursor doesn't change to pointing finger during hover over. Last attempt was to try =HYPERLINK("#'Sheet1'!E3";"Run macro")-function and using it with 'Worksheet_FollowHyperlink'. However, in this case event does not trigger if hyperlink points to current sheet.

So any new ideas or maybe refining some of these to overcome problems I have described?

SierraOscar SierraOscar · Accepted Answer · 2015-11-25T11:57:10

Here's an example of how you could do this:

Add a button (MSForm, not ActiveX) in the cell. Make sure the top left corner of the button is inside the cell for the row you want to refer to.
Assign the macro (let's say "MM" here for simplicity)

In the Macro:

Sub MM()
    Dim rowNumber As Long
    rowNumber = Shapes(Application.Caller).TopLeftCell.Row

    Debug.Print Range("A" & rowNumber).Value
End Sub

How to bind VBA macro to specific cell?

1 Answers