PDA

View Full Version : Solved: Identify Button that has been clicked



Hawkin
05-25-2010, 03:33 AM
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
05-25-2010, 04:08 AM
One button per title? Why?

Paul_Hossler
05-25-2010, 08:34 AM
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
05-25-2010, 08:44 AM
I think what you're looking for is Application.Caller

GTO
05-25-2010, 09:58 AM
...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
05-26-2010, 04:36 AM
'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
05-26-2010, 05:39 AM
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
06-21-2010, 10:51 AM
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
06-21-2010, 11:49 AM
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
06-21-2010, 11:51 AM
1. There is the Workbook event Private Sub Workbook_Open()

Paul

Just wanted to write that I found it, thanks anyways :)