Identify Button that has been clicked

[Hawkin]

Hey there!
I have an excel database of my music titles and added a "play"-function to directly play the title in excel.
I want to add a play button for every title (one title per row), so it's going to be 1000+ buttons (this is the only reasonable method I could think of).
If I'd do it with Control Buttons, I'd have to add 1000+ times the code for each button to play music, so I use Forms Buttons and want to assign them all to the same macro that will start playing.

I want to program it like this:

Public Sub play_Change()
    Dim buf As String
    buf = GetButtonNameThatActivatedThisMacro()
    'Buttons are named Button1, Button2, etc. so then I retrieve the Button number out
'of the Button name and play the title in the row of this number
End Sub

Is this possible?
TIA,
Hawk

[Aussiebear]

One button per title? Why?

[Paul_Hossler]

I'd set it up do that if I double clicked the row with a music title, it'd play

Lots easier that 1000+ buttons

Paul

[mdmackillop]

I think what you're looking for is Application.Caller

[GTO]

...so I use Forms Buttons and want to assign them all to the same macro that will start playing...

Hi Hawkin,

I'm not much with Shapes, so there may be better ways, but to go along with Malcom's suggestion, you could use the Shape's names.

Option Explicit
    
Sub CalledFromFormsButton()
Select Case ActiveSheet.Shapes(Application.Caller).Name
    Case "Button 1":    MsgBox "Called from: " & ActiveSheet.Shapes(Application.Caller).Name
    Case "Button 2":    MsgBox "Called from: " & ActiveSheet.Shapes(Application.Caller).Name
    End Select
End Sub

Hope that helps,

Mark

[Paul_Hossler]

'Buttons are named Button1, Button2, etc. so then I retrieve the Button number out
'of the Button name and play the title in the row of this number

That's still 1000+ buttons on the worksheet !!!!!

Each one has to be inserted, configured, named ...

Since you want to play from Excel, I'd re-consider the 1000+ Buttons and use a more 'Excel-like' solution.

Possibly, the WS BeforeDoubleClick even to take the cell and Play the title


Paul

[mdmackillop]

I'd go along with Paul.
You could try formatting cells to resemble buttons if you really want this sort of interface, and use the Selection Change event to play the tune, change the "button" colour etc.

[Hawkin]

Thank you for your answers. I lack in knowledge about excels power, I didn't know about BeforeDoubleClick. I am using this method now, it's great.

Is there a way to execute code upon opening the file? I want to check all mp3s if they exist when I open the document.

Edit: Also I recognized, if you use the BeforeDoubleClick method and click inbetween two cells, the script doesn't trigger. Is there a workaround for this?

[Paul_Hossler]

Is there a way to execute code upon opening the file

1. There is the Workbook event Private Sub Workbook_Open()

and click inbetween two cells, the script doesn't trigger.

Aim better is about all I can tell you

Paul

[Hawkin]

1. There is the Workbook event Private Sub Workbook_Open()

Paul

Just wanted to write that I found it, thanks anyways