Public control collection?

B

Bob Phillips

You will need to New the collection in the Userform_Intialize

Set JobNameBox = New Collection

--

HTH

Bob Phillips

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

RS

I need to have a control collection that is public to other modules and
procedures.
When I declare it in the procedure it like

Private Sub UserForm_Initialize()

Dim JobNameBox As New Collection

For a = 1 To Controls.Count - 1
If Left$(Controls.Item(a).Name, 12) = "JobNameCmbBx" Then

JobNameBox.Add Controls(a)

Controls.Item(a).Clear
rw = 2
' change to job name worksheet
While Worksheets("JobNamesList").Cells(rw, 1) <> ""
Controls.Item(a).AddItem (Worksheets("JobNamesList").Cells(rw,
1) & " , " & Worksheets("JobNamesList").Cells(rw, 2))
rw = rw + 1
Wend

End If
Next

But if I move the declaration statement to a Module like

Public JobNameBox As Collection

I get the "Run-time error '91': object variable or with block variable not
set" message box.
 
Top