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
Ben

Build a generic function/sub that is called by the individual control subs,
then only need to change one.

Or use Forms controls, they can all assign the same macro.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ben" wrote in message
...
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
Ben

Label control from the Controls Toolbar must have separate event handlers -
120 of them.

However, Label control from the Forms Toolbar can be assigned to just one
event handler macro.

You lose some features with the "Forms" label control, but it may suit what
you're after.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ben" wrote in message
...
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
Ben

ok I see how to use this with the form control, but how can I assign all the
labels to just one macro?


"Rob van Gelder" wrote:

Label control from the Controls Toolbar must have separate event handlers -
120 of them.

However, Label control from the Forms Toolbar can be assigned to just one
event handler macro.

You lose some features with the "Forms" label control, but it may suit what
you're after.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ben" wrote in message
...
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
Ben

ok i have figured out how to do so and to run the multi control with the same
code, but my problem is that i need to actually change one of the attributes
of the label box itself, That is the backcolor needs to toggle on each click


"Rob van Gelder" wrote:

Label control from the Controls Toolbar must have separate event handlers -
120 of them.

However, Label control from the Forms Toolbar can be assigned to just one
event handler macro.

You lose some features with the "Forms" label control, but it may suit what
you're after.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ben" wrote in message
...
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
Ben

I don't know how to change the backcolour of labels of forms.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ben" wrote in message
...
ok i have figured out how to do so and to run the multi control with the
same
code, but my problem is that i need to actually change one of the
attributes
of the label box itself, That is the backcolor needs to toggle on each
click


"Rob van Gelder" wrote:

Label control from the Controls Toolbar must have separate event
handlers -
120 of them.

However, Label control from the Forms Toolbar can be assigned to just one
event handler macro.

You lose some features with the "Forms" label control, but it may suit
what
you're after.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ben" wrote in message
...
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
Ben

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

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

ok nevermind yes it will not work with two class modules, but it worked great
when i combined them into one class module, thank you very much your help is
very appreciated


"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

Hi Ben,

ok nevermind yes it will not work with two class modules, but it worked great
when i combined them into one class module, thank you very much your help is
very appreciated

I don't know of any reason why it wouldn't work with two class modules, but I'm
glad you got it working.

Regards

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

Stephen Bullen wrote:
I don't know of any reason why it wouldn't work with two class
modules

What did the other class module do, we wonder. If it added controls to
the worksheet it could cause the VBA project to be 'reset' e.g. all
object variables (including the Collection) set to Nothing.
Jamie.

--

Hi Jamie,

I don't know of any reason why it wouldn't work with two class
modules

What did the other class module do, we wonder. If it added controls to
the worksheet it could cause the VBA project to be 'reset' e.g. all
object variables (including the Collection) set to Nothing.

My guess is that the OP simply wasn't storing all the instances in a
collection, so one of them was being destroyed when the routine ended.

Regards

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

Hi Jamie

What did the other class module do, we wonder. If it added controls to
the worksheet it could cause the VBA project to be 'reset' e.g. all
object variables (including the Collection) set to Nothing.
Jamie.

I was interested in your passing remark and took a closer look. It seems
there is a bit more too it, at least in my testing.

With one or more Class's of controls, I find if adding worksheet controls to
the workbook that's running the code all code can terminate. It doesn't seem
to matter if the code that's adding controls is in a class or a normal
module.

However, if adding controls to another workbook I can add new controls every
which way without problem, from a single or second class of controls or in a
normal module. Further, I can increment an existing array of the class'd
controls (redim preserve) or add to a collection.

Unless I'm totally missing something (possible) it would appear there's a
fundamental difference between running code from the wb to which new
worksheet controls are being added, or to another workbook.

Regards,
Peter T

PS fwiw, I'm not the Peter to whom you've given a few other interesting
replies of late.