LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
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?

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Userform Initialize & combo box values michaelberrier Excel Discussion (Misc queries) 3 June 27th 06 04:35 PM
Initialize Userform, Populate Listbox, Dynamic RowSource? RShow Excel Programming 1 September 21st 05 07:55 PM
UserForm Initialize event doesn't fire reliably RB Smissaert Excel Programming 13 February 16th 05 01:39 AM
Qn: Define a variable in Initialize for Userform Michael Vaughan Excel Programming 4 August 21st 04 12:10 PM
Userform disappears when you try to initialize from a command button RPIJG[_60_] Excel Programming 8 July 2nd 04 08:14 PM


All times are GMT +1. The time now is 09:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"