Which button did I push
 

I have two command butons on an Excel page, both assigned
to the same macro. Let's say the function of the macro
is to make the cell to the right of the button blue shade.

How can I tell which button was pushed?

For example:

Sub WhichButton

Select Case ButtonPushed.Top
Case 100
Range("c10").interior.colorindex = 5
Case <=100
Range("c50").interior.colorindex = 5
End Select

End Sub

Thanks
Daniel

Which button did I push
 

Daniel,

Application.Caller returns the name of the calling button:

Select Case Sheet1.Buttons(Application.Caller).Top
Case Is 100
Range("c10").Interior.ColorIndex = 5
Case Is <= 100
Range("c50").Interior.ColorIndex = 5
End Select

hth,

Doug Glancy

"Daniel Bonallack" wrote in message
...
I have two command butons on an Excel page, both assigned
to the same macro. Let's say the function of the macro
is to make the cell to the right of the button blue shade.

How can I tell which button was pushed?

For example:

Sub WhichButton

Select Case ButtonPushed.Top
Case 100
Range("c10").interior.colorindex = 5
Case <=100
Range("c50").interior.colorindex = 5
End Select

End Sub

Thanks
Daniel

Which button did I push
 

Hi Daniel

If you use buttons from the Forms Tool bar you can use
Application.Caller

Sub a()
MsgBox ActiveSheet.Shapes(Application.Caller).BottomRight Cell.Address
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Daniel Bonallack" wrote in message ...
I have two command butons on an Excel page, both assigned
to the same macro. Let's say the function of the macro
is to make the cell to the right of the button blue shade.

How can I tell which button was pushed?

For example:

Sub WhichButton

Select Case ButtonPushed.Top
Case 100
Range("c10").interior.colorindex = 5
Case <=100
Range("c50").interior.colorindex = 5
End Select

End Sub

Thanks
Daniel

Which button did I push
 

Daniel,

You can use the Application.Caller property to get the name of
the button that was clicked. E.g.,

Dim SH As Shape
Set SH = ActiveSheet.Shapes(Application.Caller)
MsgBox "You clicked: " & SH.Name


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Daniel Bonallack" wrote in message
...
I have two command butons on an Excel page, both assigned
to the same macro. Let's say the function of the macro
is to make the cell to the right of the button blue shade.

How can I tell which button was pushed?

For example:

Sub WhichButton

Select Case ButtonPushed.Top
Case 100
Range("c10").interior.colorindex = 5
Case <=100
Range("c50").interior.colorindex = 5
End Select

End Sub

Thanks
Daniel

Which button did I push
 

Ron, Doug,
Thank you very much
Daniel
-----Original Message-----
Hi Daniel

If you use buttons from the Forms Tool bar you can use
Application.Caller

Sub a()
MsgBox ActiveSheet.Shapes
(Application.Caller).BottomRightCell.Address
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Daniel Bonallack" wrote in
message ...
I have two command butons on an Excel page, both
assigned
to the same macro. Let's say the function of the macro
is to make the cell to the right of the button blue
shade.

How can I tell which button was pushed?

For example:

Sub WhichButton

Select Case ButtonPushed.Top
Case 100
Range("c10").interior.colorindex = 5
Case <=100
Range("c50").interior.colorindex = 5
End Select

End Sub

Thanks
Daniel


.