How to find out which Form Control button on worksheet has been clicked?

[agarwaldvk]

Hi Everybody


This is an urgent requirement - any help would be highly valued.


I have two (2) buttons (Form Control buttons not ActiveX buttons) on a worksheet. Both run the same procedure - the idea being that different actions are taken depending upon which button was clicked.

Is there a way to find out which buttons has been clicked. I can use the 'caption' property to test that but how can I check/find out/determine which buttons has been clicked by the user?



Best regards



Deepak

[mikerickson]

Application.Caller will help with that.

[VBA]Sub myButtonSub()
Dim ButtonPressed As Shape
If TypeName(Application.Caller) = "String" Then
MsgBox "Button named " & Application.Caller & "was pressed"

Set ButtonPressed = ActiveSheet.Shapes(Application.Caller)
MsgBox "That button is located at " & ButtonPressed.TopLeftCell.Address
Else
MsgBox "routine not called by a control"
End If

End Sub[/VBA]

[agarwaldvk]

Hi MikeRickson


That works beautifully. Greatly appreciated. Thanks a zillion for that!

Now, could you please help me understand the code. I have never used either of the "TypeName" and the "Application.Caller" functions. Where are they generally used and how do they work. It will help me in my understanding of the same and hopefully make me a better programmer in the future.



Best regards



Deepak

[mikerickson]

TypeName is a VBA function that returns the type of a variable or of an object.

[VBA]Dim x As Variant

MsgBox TypeName(x): Rem Empty

x = 2
MsgBox TypeName(x): Rem Integer

x = "s"
MsgBox TypeName(x): Rem String

Set x = Range("A1")
MsgBox TypeName(x): Rem Range[/VBA]

Application.Caller indicates how control passed from the Excel interface to VBA. If by a spreadsheet calling a UDF, it will be a Range. If by a sheet control, a string. If F5 from the VB editor, and Error.

VBA Help has more on both Typename and Application.Caller

[agarwaldvk]

Thanks Rick!

I will have a look at it!


Deepak