Code that "knows" which button has been pressed?

[TheWennerWoman]

Hello, hope someone can help.

We have a file and each row has a hyperlink in column F. Each hyperlink points to a different Word document that is relevant to that particular record.

We have had a number of occasions where the spreadsheet has gone rogue and when Excel recovers it all of the hyperlinks then point to some location on the c: drive (Appdata / Local, just a random location).

The workaround, I think, is to add a button to each cell in column F and have it open the file which will be named in column N.

So rather than have a macro for each button, is it possible to have one macro that will open the right file? So row 2 button might call C:\test1.doc whereas row 9 button might call c:\hello.doc.

Essentially, the macro needs to "know" which of the hundreds of buttons has been pressed.

Hope that makes sense, thank you for reading.

 

[RoryA]

Yes as long as you use Form buttons rather than ActiveX. In that case, Activesheet.Buttons(Application.Caller) would give you a reference to the specific button, and you can assign the same macro to all the buttons.

 

[steve the fish]

Dont have hundreds of buttons. You could use for example a double click event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

MsgBox "Row " & Target.Row

End Sub

 

[FormR]

If the spreadsheet has a habit of "going rogue" adding hundreds of buttons is unlikely to help with the stability.

You could also consider using the =HYPERKLINK() function.