multiple command buttons leading to 1 event

[Kate]

Hi,
I have checked the site:
http://j-walk.com/ss/excel/tips/tip44.htm

but the difference with my problem is that I dont have a user form. I
just have an excel sheet with some command buttons on it. When you
click any command button, I want the name of the caption on that
command button to be selected so that i can sort data in another sheet
using that caption.

I know i should be able to modify the above code somehow, but don't
know what to put instead of "For Each ctl In UserForm1.Controls". I
want this to be a very basic and quick thing. If I wanted to be fancy
(which I don't) I would just create an access database.

Thanks
Kate

 

[Dave Peterson]

Each of those commandbuttons from the Control toolbox toolbar has its own click
event. You can just pass what you want to the common routine.

For instance, this passes the commandbutton itself:

(behind the worksheet)
Option Explicit
Private Sub CommandButton1_Click()
Call DoTheSort(Me.CommandButton1)
End Sub
Private Sub CommandButton2_Click()
Call DoTheSort(Me.CommandButton2)
End Sub
Private Sub CommandButton3_Click()
Call DoTheSort(Me.CommandButton2)
End Sub

In a general module:
Option Explicit
Sub DoTheSort(CMDBtn As MSForms.CommandButton)
Dim myStr As String
myStr = CMDBtn.Caption
MsgBox myStr
End Sub

====================
An alternative is to use the button from the Forms toolbar and use a common
routine in a general module. Then assign this macro to each of the buttons.

Option Explicit
Sub DoTheSort()
Dim myBTN As Button
Set myBTN = ActiveSheet.Buttons(Application.Caller)
MsgBox myBTN.Caption
End Sub

 

[Dave Peterson]

And I made a typo on the 3rd commandbutton. I passed it the 2nd one.

Be more careful than I was!

 

[Kate]

Thank you, yes this will work, but I was hoping to avoid having to
have code for each button individuallly because I have about 50
buttons...

 

[Dave Peterson]

Maybe using the button from the Forms toolbar would be easier?