[RESOLVED] ofc excel 2007- How to retriev value of which button is clicked?

Hi all,

Suppose in an excel sheet manually i m making 3 buttons and assigning them to same macro. Whenever any of the button is clicked, inside macro i should do three different operations. So that i want to know which button is clicked. the name of the button i shud retriev to a variable so that i can proceed...

If you would let the macro take an argument, you could assign different value for each button.

Code:

---

'Your button_click code
Private Sub CommandButton1_Click()
Const i = 1
'Call your Macro
MyMacro (i)
End Sub

'Your Macro
Private Sub MyMacro(Argument As Integer)
Select Case Argument
    Case Is = 1
        'CommandButton1 was clicked!
    Case Else
        'Whatever
    End Select
End Sub

---

Its a good option.. but..
This is like assigning differnt macro for all button..
It cant retriev the name of the button directly to a macro while clicking it.. how dat is possible..?

If you do not handover anything with the call of the macro, you will not know!
you could reduce the code to:

Code:

---

'Your button_click code
Private Sub CommandButton1_Click()
'Call your Macro
MyMacro (1)
End Sub
Private Sub CommandButton2_Click()
'Call your Macro
MyMacro (2)
End Sub
'Your Macro
Private Sub MyMacro(Argument As Integer)
Select Case Argument
    Case Is = 1
        'CommandButton1 was clicked!
    Case Is = 2
        'CommandButton2 was clicked!
    Case Else
        'Whatever
    End Select
End Sub

---

Yea thats true..
But if i want to reitriev value which is written in the button(as its label) then for selection dat button and finding out the text written in it i want to know wt s d button name created by user.
it may be Button 1 or 2 or 3 or 4....
it will varry..
so if getting that value alone and printing in MyMacro itself will be good enough right?

You are missing the point!
You will have acces to such info if you are within the _Click Sub.
However you want to call a macro which is outside of the _Click Sub, this macro will only "know" what you are telling it!

oh is it.. thats great then.. thank you..
I thot that sub _click is also just like macro..

you can do what you are asking, but it means having a class collection of buttons, then each button is added to the class and when clicked can retrieve the index from within the class collection, this is very easy to do in vb6, where you can just add an array of controls to the form at design time, or add at runtime, but control arrays are not available in VBA

there are examples in this forum on using a class to have a collection of controls, one was about a calendar where the day clicked on was returned in a class, a search should return some samples