running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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

running same code with multiple controls
 

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.

--

running same code with multiple controls
 

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

running same code with multiple controls
 

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.

running same code with multiple controls
 

Hi Peter,

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.

Absolutely. Adding an ActiveX control (from the Control Toolbox) to a sheet
causes the project of the workbook containing that sheet to recompile. If
that workbook is the same one that's running the code, bad things happen.
If that's a separate workbook, it'll just reset any module-level and global
variables in the project (i.e. including those used to hold 'withevents'
class references).

Regards

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

running same code with multiple controls
 

Hi Stephen,

Ah, that solves a lot of mysteries. Obvious now you explained it.

Thanks,
Peter T

"Stephen Bullen" wrote in message
...
Hi Peter,

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.

Absolutely. Adding an ActiveX control (from the Control Toolbox) to a
sheet
causes the project of the workbook containing that sheet to recompile. If
that workbook is the same one that's running the code, bad things happen.
If that's a separate workbook, it'll just reset any module-level and
global
variables in the project (i.e. including those used to hold 'withevents'
class references).

Regards

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

running same code with multiple controls
 

Peter T wrote:
Ah, that solves a lot of mysteries.

Mysteries, eh? Consider this test code in a new blank workbook which
hasn't been saved:

' --- <ThisWorkbook code module ---
Option Explicit

Private m_blnIsInitialized As Boolean

Public Property Let IsInitialized(ByVal Newvalue As Boolean)
m_blnIsInitialized = Newvalue
End Property

Public Property Get IsInitialized() As Boolean
IsInitialized = m_blnIsInitialized
End Property
' --- </ThisWorkbook code module ---

' --- <Standard .bas code module ---
Option Explicit

Sub Test1()
ThisWorkbook.IsInitialized = True
Dim ws As Worksheet
Set ws = Sheet1
AddControl ws
Application.OnTime Now, "Test2"
End Sub

Private Function AddControl(ByVal ws As Worksheet) As Boolean
ws.OLEObjects.Add "Forms.CommandButton.1"
End Function

Sub Test2()
MsgBox ThisWorkbook.IsInitialized
End Sub
' --- </Standard .bas code module ---

Run Test1 and it shows False, indicating the VBA project has been
reset. Run again and it returns True. Was it the implicit setting of
the reference to the MSForms library that caused the reset?

Now change the reference to the worksheet in the code to Sheet1 and it
fails every time. Explanation?

Jamie.

--

running same code with multiple controls
 

Jamie Collins wrote:
Now change the reference to the worksheet in the code to Sheet1

Oops, seems I already did! The original line was supposed to be
Set ws = Application.Workbooks("Book1").Worksheets(1)

Jamie.

--

running same code with multiple controls
 

Jamie Collins wrote:
Peter T wrote:
Ah, that solves a lot of mysteries.

Mysteries, eh?

But I didn't say it solves all mysteries!

I have code working with controls in same workbook, code consists of:
With events Class to run similar controls.
In normal module, macro "Setup" to instansiate the class'd controls and a
public variable array pointing to the Class.
A macro "Clearup" to destroy the all instances of the class and reset all
variables to nothing or erase.
Also, a macro named "NewCtrl" that adds a new control.

From scratch or a newly opened wb I can run "NewCtrl" as many times as I
want.
I run "Setup", all working fine.
I run "ClearUp" then, to be sure, manually reset the project.

Now run "NewCtrl" again, it adds a control BUT code terminates before
reaching "End Sub"

Why should this now fail in effectively a virgin project?

So, I comment out all pointers to the class, run "NewCtrl" yet again - no
problem!

Still some mysteries, or as Stephen said - "bad things happen".

Regards,
Peter T

running same code with multiple controls
 

Hi Peter,

Still some mysteries, or as Stephen said - "bad things happen".

Or to put it another way - even if I did find out why it works in some
cases and not others, I still wouldn't trust it in a production app.

Regards

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

running same code with multiple controls
 

Hi Stephen,

Still some mysteries, or as Stephen said - "bad things happen".

Or to put it another way - even if I did find out why it works in some
cases and not others, I still wouldn't trust it in a production app.

Definately sinking in, like fast!

Regards,
Peter T