View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
natanz[_2_] natanz[_2_] is offline
external usenet poster
 
Posts: 40
Default 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?