Group click events for many form labels on worksheet

[BiGMaN]

I've got a bunch of labels (form version) on a worksheet. I want a single event to fire when any one of these is clicked.

I would be able to do this with a class event if the controls were actually on a form, but these are on the worksheet. I can do some rudimentary things with class modules, but I don't really understand what I'm doing, so I don't know if this is the way to go about it or not.

I simply don't want to have a ton of LabelXX_Click() procedures (one for each label), each pointing to my procedure with the actual code I want to run.

Thanks!

 

[Tom Urtis]

Why are you referencing class modules with the term "form version" controls, the reason I ask is, the code you need will depend on exactly which toolbar you created the labels from.

Was it from the Forms toolbar

or

Was it from the ActiveX Control Toolbox?

 

[BiGMaN]

Sorry, I was speaking from memory and was way off. They are from the "Control Toolbox" in Excel 2003.

 

[Tom Urtis]

You did not say what the name of the sheet is where these labels are embedded, so let's assume it is the sheet whose tab is named "Sheet1".

From your worksheet, press Alt+F11 to get into the Visual Basic Editor.

From the menu at the top of the VBE, click Insert > Module and paste this in:

Public myControls As Collection
 
Sub Auto_Open()
With Application
.ScreenUpdating = False
Dim oleCtl As OLEObject, ctl As Class1
Set myControls = New Collection
For Each oleCtl In Worksheets("Sheet1").OLEObjects
If TypeOf oleCtl.Object Is msforms.Label Then
Set ctl = New Class1
Set ctl.myLBL = oleCtl.Object
myControls.Add ctl
End If
Next
.ActiveWindow.WindowState = xlMaximized
.Goto Worksheets("Sheet1").Range("A1"), True
End With
End Sub

Next, from the menu at the top, click Insert > Class Module, accept the default name of Class 1, and paste this code into the class module you just created:

Public WithEvents myLBL As msforms.Label
 
Private Sub myLBL_Click()
MsgBox "Hello, my name is ''" & myLBL.Name & "''." & vbCrLf & _
"My caption is ''" & myLBL.Caption & "''." & vbCrLf & _
"My top left corner is set in cell " & myLBL.TopLeftCell.Address(0, 0) & ".", _
64, "You just clicked me, here's my info :"
End Sub

Press Alt+Q to return to the worksheet.

Save the workbook.

Close the workbook.

Open the workbook.

Now when you click a label on Sheet1 you will see a message box appear with info about that label.

Of course, the message box is just code to give you an example...from this point you can replace the message box code with whatever you want to have happen when a label is clicked on Sheet1.

And if anyone is wondering, I purposely used Autpen instead of the Worlbook_Open event, as I've found that for OLE class modules, Autpen is more reliable.

If and when you do modify the code, make sure you save the workbook, close the workbook, and reopen the workbook for the new class code to be recognized.

 

[BiGMaN]

Great, that worked fantastically. Thanks a lot Tom!