initialize userform, using a sub

I have many userforms in the macro that i am writing. most of the
initialization is the same, populating a bunch of comboboxes from a
range.

currently i am doing the initialization in the code window of all the
userforms, but i thought it would be more efficient to call a procedure
in module1 to initialize the form. but this is not working.

here is the original code from the code window of the form:

Private Sub UserForm_Initialize()
Dim ctl As Control
Dim WT
Dim DT

WT = Sheets("sheet2").Range("W_T")
DT = Sheets("sheet2").Range("D_T")

Me.Caption = ActiveCell.Value
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
ctl.List = WT
ctl.ListIndex = 0
End If
Next ctl

ComboBox12.List = DT
ComboBox12.ListIndex = 0
ComboBox23.List = DT
ComboBox23.ListIndex = 0

TextBox1.Value = 0
end sub

here is what i changed it to:
Private Sub UserForm_Initialize()
Call init_cboxes(pg_a2)
Dim WT
Dim DT

WT = Sheets("sheet2").Range("W_T")
DT = Sheets("sheet2").Range("D_T")

ComboBox7.List = DT
ComboBox7.ListIndex = 0

TextBox1.Value = 0
End Sub

where sub init_cboxes looks like this:

Public Sub init_cboxes(ByVal MyForm As UserForm)
Dim ctl As Control
Dim WT
Dim DT

WT = Sheets("sheet2").Range("W_T")
DT = Sheets("sheet2").Range("D_T")

MyForm.Caption = ActiveCell.Value
For Each ctl In MyForm.Controls
If TypeName(ctl) = "ComboBox" Then
ctl.List = WT
ctl.ListIndex = 0
End If
Next ctl
End Sub

the code runs, but it doesn't seem to pass the values back to the
userform. Is it possible to do this?

I have no idea what the Pg_a2 in

Call init_cboxes(pg_a2)

is, but if I changed it to the userform object,

Call init_cboxes(Me)

it worked fine for me


--

HTH

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


"natanz" <[email protected]> wrote in message
news:[email protected]...
> I have many userforms in the macro that i am writing. most of the
> initialization is the same, populating a bunch of comboboxes from a
> range.
>
> currently i am doing the initialization in the code window of all the
> userforms, but i thought it would be more efficient to call a procedure
> in module1 to initialize the form. but this is not working.
>
> here is the original code from the code window of the form:
>
> Private Sub UserForm_Initialize()
> Dim ctl As Control
> Dim WT
> Dim DT
>
> WT = Sheets("sheet2").Range("W_T")
> DT = Sheets("sheet2").Range("D_T")
>
> Me.Caption = ActiveCell.Value
> For Each ctl In Me.Controls
> If TypeName(ctl) = "ComboBox" Then
> ctl.List = WT
> ctl.ListIndex = 0
> End If
> Next ctl
>
> ComboBox12.List = DT
> ComboBox12.ListIndex = 0
> ComboBox23.List = DT
> ComboBox23.ListIndex = 0
>
> TextBox1.Value = 0
> end sub
>
> here is what i changed it to:
> Private Sub UserForm_Initialize()
> Call init_cboxes(pg_a2)
> Dim WT
> Dim DT
>
> WT = Sheets("sheet2").Range("W_T")
> DT = Sheets("sheet2").Range("D_T")
>
> ComboBox7.List = DT
> ComboBox7.ListIndex = 0
>
> TextBox1.Value = 0
> End Sub
>
> where sub init_cboxes looks like this:
>
> Public Sub init_cboxes(ByVal MyForm As UserForm)
> Dim ctl As Control
> Dim WT
> Dim DT
>
> WT = Sheets("sheet2").Range("W_T")
> DT = Sheets("sheet2").Range("D_T")
>
> MyForm.Caption = ActiveCell.Value
> For Each ctl In MyForm.Controls
> If TypeName(ctl) = "ComboBox" Then
> ctl.List = WT
> ctl.ListIndex = 0
> End If
> Next ctl
> End Sub
>
> the code runs, but it doesn't seem to pass the values back to the
> userform. Is it possible to do this?
>

thanks again. pg_a2 is the name of the userform. when i put that in
there it didn't work, but when i put "me" in the parens it worked. why
is that?

In order to pass variables between procedures in the same module, they need
to be DIMensioned outside of the procedures, usually at the top of a regular
code module:
Option Explicit
Dim WT as Range

Sub MySub ()
.........
End Sub

To pass variables between modules, including UserForms, Dim them as above,
but as Public:
Option Explicit
Public WT as Range

Sub MySub ()
.........
End Sub

That will pass variables back to the UserForm.
Mike F
"natanz" <[email protected]> wrote in message
news:[email protected]...
>I have many userforms in the macro that i am writing. most of the
> initialization is the same, populating a bunch of comboboxes from a
> range.
>
> currently i am doing the initialization in the code window of all the
> userforms, but i thought it would be more efficient to call a procedure
> in module1 to initialize the form. but this is not working.
>
> here is the original code from the code window of the form:
>
> Private Sub UserForm_Initialize()
> Dim ctl As Control
> Dim WT
> Dim DT
>
> WT = Sheets("sheet2").Range("W_T")
> DT = Sheets("sheet2").Range("D_T")
>
> Me.Caption = ActiveCell.Value
> For Each ctl In Me.Controls
> If TypeName(ctl) = "ComboBox" Then
> ctl.List = WT
> ctl.ListIndex = 0
> End If
> Next ctl
>
> ComboBox12.List = DT
> ComboBox12.ListIndex = 0
> ComboBox23.List = DT
> ComboBox23.ListIndex = 0
>
> TextBox1.Value = 0
> end sub
>
> here is what i changed it to:
> Private Sub UserForm_Initialize()
> Call init_cboxes(pg_a2)
> Dim WT
> Dim DT
>
> WT = Sheets("sheet2").Range("W_T")
> DT = Sheets("sheet2").Range("D_T")
>
> ComboBox7.List = DT
> ComboBox7.ListIndex = 0
>
> TextBox1.Value = 0
> End Sub
>
> where sub init_cboxes looks like this:
>
> Public Sub init_cboxes(ByVal MyForm As UserForm)
> Dim ctl As Control
> Dim WT
> Dim DT
>
> WT = Sheets("sheet2").Range("W_T")
> DT = Sheets("sheet2").Range("D_T")
>
> MyForm.Caption = ActiveCell.Value
> For Each ctl In MyForm.Controls
> If TypeName(ctl) = "ComboBox" Then
> ctl.List = WT
> ctl.ListIndex = 0
> End If
> Next ctl
> End Sub
>
> the code runs, but it doesn't seem to pass the values back to the
> userform. Is it possible to do this?
>

another question:
on my userforms i have multiple comboboxes. whenever one of them is
changed i use the combobox*_change event to call a procedure. Is there
a way to generalize this event, so that anytime a combobox is changed
the procedure will be called, without having to have a separate
procedure for each combobox?

Presumably, because pg_a2 is a string naming the form, Me is a userform
object, and you common routine expects a userform object.

--

HTH

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


"natanz" <[email protected]> wrote in message
news:[email protected]...
> thanks again. pg_a2 is the name of the userform. when i put that in
> there it didn't work, but when i put "me" in the parens it worked. why
> is that?
>

He is not passing them between the procedures, but red-defining new ones in
each procedure!

--

HTH

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


"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> In order to pass variables between procedures in the same module, they
need
> to be DIMensioned outside of the procedures, usually at the top of a
regular
> code module:
> Option Explicit
> Dim WT as Range
>
> Sub MySub ()
> ........
> End Sub
>
> To pass variables between modules, including UserForms, Dim them as above,
> but as Public:
> Option Explicit
> Public WT as Range
>
> Sub MySub ()
> ........
> End Sub
>
> That will pass variables back to the UserForm.
> Mike F
> "natanz" <[email protected]> wrote in message
> news:[email protected]...
> >I have many userforms in the macro that i am writing. most of the
> > initialization is the same, populating a bunch of comboboxes from a
> > range.
> >
> > currently i am doing the initialization in the code window of all the
> > userforms, but i thought it would be more efficient to call a procedure
> > in module1 to initialize the form. but this is not working.
> >
> > here is the original code from the code window of the form:
> >
> > Private Sub UserForm_Initialize()
> > Dim ctl As Control
> > Dim WT
> > Dim DT
> >
> > WT = Sheets("sheet2").Range("W_T")
> > DT = Sheets("sheet2").Range("D_T")
> >
> > Me.Caption = ActiveCell.Value
> > For Each ctl In Me.Controls
> > If TypeName(ctl) = "ComboBox" Then
> > ctl.List = WT
> > ctl.ListIndex = 0
> > End If
> > Next ctl
> >
> > ComboBox12.List = DT
> > ComboBox12.ListIndex = 0
> > ComboBox23.List = DT
> > ComboBox23.ListIndex = 0
> >
> > TextBox1.Value = 0
> > end sub
> >
> > here is what i changed it to:
> > Private Sub UserForm_Initialize()
> > Call init_cboxes(pg_a2)
> > Dim WT
> > Dim DT
> >
> > WT = Sheets("sheet2").Range("W_T")
> > DT = Sheets("sheet2").Range("D_T")
> >
> > ComboBox7.List = DT
> > ComboBox7.ListIndex = 0
> >
> > TextBox1.Value = 0
> > End Sub
> >
> > where sub init_cboxes looks like this:
> >
> > Public Sub init_cboxes(ByVal MyForm As UserForm)
> > Dim ctl As Control
> > Dim WT
> > Dim DT
> >
> > WT = Sheets("sheet2").Range("W_T")
> > DT = Sheets("sheet2").Range("D_T")
> >
> > MyForm.Caption = ActiveCell.Value
> > For Each ctl In MyForm.Controls
> > If TypeName(ctl) = "ComboBox" Then
> > ctl.List = WT
> > ctl.ListIndex = 0
> > End If
> > Next ctl
> > End Sub
> >
> > the code runs, but it doesn't seem to pass the values back to the
> > userform. Is it possible to do this?
> >
>
>

Yes, a bit tricky, but doable.

Firsat, add a class module to your project and rename it clsUserformEvents.
Add this code to it

Option Explicit

Public WithEvents mCBGroup As msforms.ComboBox

Private Sub mCBGroup_Change()
MsgBox mCBGroup.Name & " has been changed"
End Sub

Then add this module variable to the top of your userform code

Private mcolEvents As Collection

and this line to your Userform_Initialize event at the end

CBGroup_Initilalize

and then add this procedure into your userform code module

Private Sub CBGroup_Initialize()
Dim cCBEvents As clsUserformEvents
Dim ctl As msforms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
Set cCBEvents = New clsUserformEvents
Set cCBEvents.mCBGroup = ctl
mcolEvents.Add cCBEvents
End If
Next

End Sub


--

HTH

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


"natanz" <[email protected]> wrote in message
news:[email protected]...
> another question:
> on my userforms i have multiple comboboxes. whenever one of them is
> changed i use the combobox*_change event to call a procedure. Is there
> a way to generalize this event, so that anytime a combobox is changed
> the procedure will be called, without having to have a separate
> procedure for each combobox?
>

Hello Natanz,

Copy this code into a module in your project. Create 2 User Forms in your project, each with a ComboBox on it.

After you create the User Forms, run the macro Test. The first form will be displayed and the ComboBox will be loaded. Close the Form and the next one will be displayed with the ComboBox loaded also. This should get you going.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

thanks for this, I will take me a while to absorb all of this, but i
think i get the basic idea. Can you confirm my understanding of a few
points.
1) in the line "Private Sub mCBGroup_Change()" the word change could
be any of the applicable events for that class, initialize,
beforeupdate, afterupdate, etc?
2) and in the following line "MsgBox mCBGroup.Name & " has been
changed" ". That's where i would put whatever code or procedure i want
to happen with that event.

ok i think those are pretty obvious. now something a little harder.
the procedure that is called from each of the combobox_ change event is
called recalc(). It is slightly different with each userform, but the
same for each combobox within a userform. if the code in the class
module calls procedure recalc(), will it look in the userform code
window for the recalc() procedure, or will it be looking in the class
module, or will it be looking in the main module. I am not sure if i
am making this clear, but if you can figure out what i am talking
about, it would be great to get some more of your very useful advice.

"natanz" <[email protected]> wrote in message
news:[email protected]...
> thanks for this, I will take me a while to absorb all of this, but i
> think i get the basic idea. Can you confirm my understanding of a few
> points.
> 1) in the line "Private Sub mCBGroup_Change()" the word change could
> be any of the applicable events for that class, initialize,
> beforeupdate, afterupdate, etc?

In principle, yes, in practice no. VBA does not expose all events through
this method, so some, including Before/AfterUpdate, are not available. You
need to go into the class module, selecte mCBGroup from the Object dropdown,
and see what events are expopsed by looking at the Procedure dropdown.

BTW, it is applicable events for that object, not the class, as you could
define multiple objects i that same class.

> 2) and in the following line "MsgBox mCBGroup.Name & " has been
> changed" ". That's where i would put whatever code or procedure i want
> to happen with that event.

Correct.

> ok i think those are pretty obvious. now something a little harder.
> the procedure that is called from each of the combobox_ change event is
> called recalc(). It is slightly different with each userform, but the
> same for each combobox within a userform. if the code in the class
> module calls procedure recalc(), will it look in the userform code
> window for the recalc() procedure, or will it be looking in the class
> module, or will it be looking in the main module. I am not sure if i
> am making this clear, but if you can figure out what i am talking
> about, it would be great to get some more of your very useful advice.

It will look in the class module, and in a standard code module, but not in
the userform code module.
>

it's been a long time since i was on this. but I just had some time to
get back to it. if you recall, i was trying to generalize the event
that happens when a combobox changes, so i didn't have a separate
procedure for each combobox. I haven't completely absorbed the code
you gave me last time, but i know i have one problem. In their current
form, my combobox change event procedure looks like this:

Private Sub ComboBox1_Change()
' Call recalc(Me)
'End Sub

Using the procedure in that you outlined where the event procedure is
called in this class module, how can i send the "me", which in this
case is the whole userform?

again, i appreciate all the help you have given me up to now.