Open Workbook and Fill Combobox Quest?

[ribhead]

I am trying to fill a combobox from a closed workbook without the user noticing. The following is my code and I'm not sure how to hold on to the data once I close the WB. Any help would be OneDERFULLLLLL.

Private Sub UserForm_Initialize()
Dim ListItems As Range, SourceWB As Workbook
With Me.ComboBox1
.Clear
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("H:\WIP2\Teams.xls", False, True)
lastrow = Rows.Cells(65536, 1).End(xlUp).Row
Set ListItems = SourceWB.Worksheets(1).Range("A1:A" & lastrow)
.RowSource = ListItems
End With
Application.ScreenUpdating = True
Set SourceWB = Nothing
SourceWB.Close False
End Sub

For just $19.95 you too can have a beautiful smile.

 

[JPMontreal]

Hi,

You could copy first the data into a hidden sheet and then populate, from it, your combo.


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[DrBowes]

Or create a collection and add the items from SourceWB.