Group click events for many form labels on worksheet

B

BiGMaN

New Member
Joined
Mar 2, 2007
Messages
27
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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?
 
Upvote 0
Sorry, I was speaking from memory and was way off. They are from the "Control Toolbox" in Excel 2003.
 
Upvote 0
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:

Code: 
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:

Code: 
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 Auto_Open instead of the Worlbook_Open event, as I've found that for OLE class modules, Auto_Open 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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Checking for Worksheets that already exist
2
Replies
11
Views
324
Logit
Logit
T
  • Question Question
Custom ribbon Delete button status at runtime
Replies
6
Views
291
tiredofit
T
P
  • Question Question
Multiple textbox calculation.
Replies
2
Views
201
Fluff
Fluff
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
113
6StringJazzer
6StringJazzer
P
  • Solved
New Worksheet Hyperlinks
2
Replies
12
Views
259
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,587
Messages
6,179,734
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top