How can one click any object in Excel and have VB tell you the name of the
object that you clicked. For instance, if I click label1, VB gives me a
message telling me that I clicked label1, and if it was label2, it says
label2. The trick is that I would like to do this by using only one macro,
and not two as would be easy for 2 labels. The reason behind this is that
when using a lot of labels, the macros are to many to write.

Regards
Rex

why don't you use a class module where a single macro could do the job
for any label ?
By the way, in this macro, it's an easy job to make the clicked label's
name appear.

Regards
Rex a écrit :
How can one click any object in Excel and have VB tell you the name of the
object that you clicked. For instance, if I click label1, VB gives me a
message telling me that I clicked label1, and if it was label2, it says
label2. The trick is that I would like to do this by using only one macro,
and not two as would be easy for 2 labels. The reason behind this is that
when using a lot of labels, the macros are to many to write.

Regards
Rex

in a class module named Class1 you write

Public WithEvents lbl As MSForms.Label
Private Sub lbl_Click()

MsgBox lbl.Caption

End Sub

In the userform module you write

Dim arrlbl(1 To 2) As New Classe1

Private Sub UserForm_Initialize()

Set arrlbl(1).lbl = Me.Label1
Set arrlbl(2).lbl = Me.Label2

End Sub

Doing so, you will get the label's caption for any label you declared in
the Sub UserForm_Initialize.
Of course, if there are many labels, you can make a loop on the whole of
them using a ReDim Preserve instruction for filling the Array arrlbl. If
you choose this last solution, don't forget to declare your Array with
no bounds.

Regards

anonymousA a écrit :
why don't you use a class module where a single macro could do the job
for any label ?
By the way, in this macro, it's an easy job to make the clicked label's
name appear.

Regards
Rex a écrit :

How can one click any object in Excel and have VB tell you the name of
the object that you clicked. For instance, if I click label1, VB
gives me a message telling me that I clicked label1, and if it was
label2, it says label2. The trick is that I would like to do this by
using only one macro, and not two as would be easy for 2 labels. The
reason behind this is that when using a lot of labels, the macros are
to many to write.

Regards
Rex

Hi Rex.

Check the Caller proprty in VBA help.

You can use this property to assign (or call) one common macro to each
control and have the macro respond differently according to which control is
clicked.

For example, something schematically like:

Sub Tester02()
MsgBox Application.Caller

Select Case Application.Caller
Case "Label 1"
'Do something
MsgBox "hello"
Case "Label 2"
' Do something else
Case "Label 3"
'Do something else
Case Else
'Do something
End Select

End Sub

---
Regards,
Norman



"Rex" wrote in message
...
How can one click any object in Excel and have VB tell you the name of the
object that you clicked. For instance, if I click label1, VB gives me a
message telling me that I clicked label1, and if it was label2, it says
label2. The trick is that I would like to do this by using only one
macro,
and not two as would be easy for 2 labels. The reason behind this is that
when using a lot of labels, the macros are to many to write.

Regards
Rex