running same code with multiple controls

[Guest]

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
Ben

 

[Bob Phillips]

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)

 

[Rob van Gelder]

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.

 

[Guest]

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

 

[Guest]

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]

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

 

[Stephen Bullen]

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

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

 

[Guest]

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?????

 

[Guest]

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]

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

 

[onedaywhen]

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.

--

 

[Stephen Bullen]

Hi Jamie,

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

 

[Peter T]

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.

 

[Stephen Bullen]

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

 

[Peter T]

Hi Stephen,

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

Thanks,
Peter T

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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.

--

 

[Peter T]

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

 

[Stephen Bullen]

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

 

[Peter T]

Hi Stephen,

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