repeating/looping through code

[northernstar]

hi

i have set up a form and wrote some code for some comboboxes but found that i am repeating the code for many comboboxes and just amending the names

this is not a major problem, just time consuming and more prone to errors

i am sure there must be a better means of doing this

unsure as the code is done via the combobox_change procedure

any ideas which can point me in the right direction

thanks

[lucas]

Be more specific....there are 2 userforms and lots of comboboxes. We can't see over your shoulder or read your mind.

[Norie]

Which comboboxes do you want to do this for?

[northernstar]

ok no problem

it is UserForm2

comboboxes
piston1 to piston6 the code is the same except it references to the different comboboxes

piston1 - units1 - applied1 - corrected1 - reading1 - error1
are all crossed reference to one another, hope this makes sense

thought i could create a loop to go through the procedure for each change to the comboboxes piston1 to 6

as you can see its a lot of typing

hope this helps
thanks

[Bob Phillips]

Create a class module called clsUserFormEvents with this code

[vba]

Option Explicit

Public WithEvents mComboGroup As msforms.ComboBox

Private Sub mComboGroup_Change()
MsgBox mComboGroup.Name & " has been pressed"
End Sub
[/vba]

and then add this to your userform

[vba]

Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cCboEvents As clsUserFormEvents
Dim ctl As msforms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
Set cCboEvents = New clsUserFormEvents
Set cCboEvents.mComboGroup = ctl
mcolEvents.Add cCboEvents
End If
Next
End Sub
[/vba]

[northernstar]

not sure i understand this but i will follow your instructions and let you know how i get on

never used a class module before and i am not sure where i put the code i would like to loop through

sure you will guide me through with great ease

thanks again

[lucas]

insert-class module
just like insert-module

must be named: clsUserFormEvents

[northernstar]

i have entered the code and the class module

and when i run the useform i get the following error

user-defined type not defined and the following code is highlighted in yellow
Private Sub UserForm_Initialize()
Dim cCboEvents As clsUserFormEvents

not sure about how this works?

[northernstar]

thanks lucas just saw your post, done that and now getting the following error

variable not defined
mcolEvents

thanks

[Bob Phillips]

Did you change the name of the class to clsUserFormEvents?

mColEvents is a variable outside of the procedures, as shown in the code I posted.

[northernstar]

yes i changed the name of the class

still got this variable error, cant see it defined outside the code you posted

please remember i have never used a class module before

where does my orginal code go and how do it get refered to?

thanks for being patient

thanks

[Bob Phillips]

[vba]

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' it is HERE
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim mcolEvents As Collection
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Private Sub UserForm_Initialize()
Dim cCboEvents As clsUserFormEvents
Dim ctl As msforms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
Set cCboEvents = New clsUserFormEvents
Set cCboEvents.mComboGroup = ctl
mcolEvents.Add cCboEvents
End If
Next
End Sub
[/vba]

You didn't have any original code, but you would add the evnt code tothe class module.

[lucas]

[VBA]Dim mcolEvents As Collection[/VBA]
This goes in the userform code module just under Option Explicit which should be the very first line of code in the userform code module....it is not within any sub routines...out there by itself just below option explicit.

[northernstar]

excuse me for being a little slow on this

but does my code go around

Dim mcolEvents As Collection

as indicated by <<<<<<<<<<

thanks

my code was under _change procedures for each combobox so how do i apply this now?

[northernstar]

got the form up and running now, just need to get the _change procedures to run now

could you please point me in the right direction

would it be possible to expalin thid code a little simplier?
[VBA]Private Sub UserForm_Initialize()
Dim cCboEvents As clsUserFormEvents
Dim ctl As msforms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
Set cCboEvents = New clsUserFormEvents
Set cCboEvents.mComboGroup = ctl
mcolEvents.Add cCboEvents
End If
Next
End Sub[/VBA]

[Bob Phillips]

How can that work when it didn't before without declaring mColEevents?

Read what is written.

I said ... You didn't have any original code, but you would add the event code to the class module ... That is what the class module is for, to handle the control array events.

[northernstar]

so i put the code into the class module

i can see how it loops through the comboBoxes and how it knows which one it is in, but how can it determine that ComboBox (Piston1) has beed changed and that label (Units1) needs to be updated

example

when the user selects E346 from ComboBox(Piston1) then the label(Units1) needs to be equal to "lbf/in?" and Label(Corrected1) needs to be equal to a calculation

hope this makes sense to you

i have the form working (not quite finished) with the code all entered long hand and not using a class module
but this is very long winded as you can see from the code in the file i have sent you and that is not finished yet

cant seem to get my head around the class module

where can i get some very basic notes on this topic

thanks

[Bob Phillips]

so i put the code into the class module

Yes!

i can see how it loops through the comboBoxes and how it knows which one it is in, but how can it determine that ComboBox (Piston1) has beed changed and that label (Units1) needs to be updated

The loop is only adding the combos to the control array, because you have linked them with events, VBA will pass control to the mComboGroup event within the class for each combo change.

It just knows what the combo that was changed is (just as it knows that in a normal situation), but it will have no idea that YOU want Units1 updated if it is Piston1, YOU will have to code that.

This technique is only useful if each combo does the same sort of thing, as you suggested at the start, not if they all do different things.

example

when the user selects E346 from ComboBox(Piston1) then the label(Units1) needs to be equal to "lbf/in?" and Label(Corrected1) needs to be equal to a calculation

hope this makes sense to you

As I said, you will have to code it. You would have to in the individual combo event procedures, the difference here is that if they all update different labels, you will need to test which one was updated (and then I question why bother with this technique).

i have the form working (not quite finished) with the code all entered long hand and not using a class module
but this is very long winded as you can see from the code in the file i have sent you and that is not finished yet

I never saw any code in that file.

cant seem to get my head around the class module

where can i get some very basic notes on this topic

You won't, it isn't basic, and there is a dearth of good quality info IMO. And this is a specialist technique, the class module is only required to use with events.

[northernstar]

ok thanks for that