Assigning label caption to a variable

[ASA]

Hello Everyone,

I have a worksheet where I have created few labels, renamed them and assigned them one particular macro with a VB Code as follows:



VerticalPosition = 112.5

For k = 9 To 500

If ActiveSheet.Cells(k, 2) <> "" Then

VerticalPosition = VerticalPosition + 12.75

ActiveSheet.Labels.Add(7.5, VerticalPosition, 100, 13.5).Select

Selection.Characters.Text = ActiveSheet.Cells(k, 2).Value 'renaming label

Selection.OnAction = "Macro1"

End If

Next k




Please note that all the labels have been assigned the same macro (i.e. Macro1).

Now, when the user clicks on any one label in the worksheet, Macro1 assigned to it is executed.

I want to use the caption of the label on which the user clicked in the Macro1 to be executed.

Can the caption of the clicked label be assigned to a public variable and then be used in the Macro1 to follow? If yes, then kindly post some illustrative code.

Please note that the labels are being created in worksheet by a logic code. So, their number may vary. Even the assigned label names may vary.

Regards,
ASA

[Bob Phillips]

Is this what you want?



MsgBox ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text

[ASA]

Thanks for your prompt reply xld.
This works fine!

Regards,
ASA