Thank you,

The code to add the shapes to the collection works correctly, but the class
module is not called once a label is clicked?? Will this code not work if I
have more than one class module already installed?????

"Stephen Bullen" wrote:

Hi Ben,

Is there a workaround to run the same code on multiple controls EMBEDDED INTO
A WORKSHEET. I have the workaround for userforms but say for instance. I have
about 120 Labels on a sheets, and when I click on any of them I want them to
all run the exact same code, changing their background color???? Very
annoying to have 120 seperate codes, especially now that I need to change
each and every one to accomodate a new change. Help Please :P

You can do this using a class module to handle the labels' click events. Start
with a class module called CLblEvents, containing:

Public WithEvents mlblLabel As MSForms.Label

Private Sub mlblLabel_Click()
mlblLabel.BackColor = IIf(Rnd() 0.5, vbRed, vbBlue)
End Sub


Then we create a new instance of the class for each label, e.g. in the
worksheet_activate event:

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()

Dim clsLblEvents As CLblEvents
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.Label Then
Set clsLblEvents = New CLblEvents
Set clsLblEvents.mlblLabel = shp.OLEFormat.Object.Object
mcolEvents.Add clsLblEvents
End If
End If
Next

End Sub


Now, all labels (from the control toolbox) on the sheet will respond to being
clicked.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk