filling combo box

B

Bob Phillips

Mike,

Open the file
Either copy the data to the combobox file and point the combobox at that
data, or add them to the combobox
Close the file

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Mike

Is there a site with sample code? This is a combo box on the worksheet, not
a userform.
 
B

Bob Phillips

Mike,

This is the sort of thing.

This example loads the combobox directly

Workbooks.Open Filename:="c:\myTest\Testfile_1.xls"
With ThisWorkbook.Worksheets("Sheet1")
For i = 1 To 10
.ComboBox1.AddItem ActiveSheet.Range("B" & i).Value
Next i
.ComboBox1.ListIndex = 0
End With
ActiveWorkbook.Close

This example copies the data to this workbook and directs the combobox to
that data

Workbooks.Open Filename:="c:\myTest\Testfile_1.xls"
With ThisWorkbook.Worksheets("Sheet1")
ActiveSheet.Range("D1:D10").Copy .Range("A1")
.ComboBox1.ListFillRange = "A1:A10"
.ComboBox1.ListIndex = 0
End With
ActiveWorkbook.Close


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Mike

Thanks for the code. I would prefer to use the first code example. But I
am having trouble programming with columncount property. I have 3 columns
to be displayed in the combobox.
I do I do that? BoundColumn will be set to 1.

Lastly, how do I sort the values within the combo box?
 
T

Tom Ogilvy

This pseudo code should show you how to do it.

Dim rng as Range
Workbooks.Open Filename:="c:\myTest\Testfile_1.xls"
With Activesheet.
.sort Key1:=.Range("A1'), _
Order1:=xlAscending, Header:=xlNo
set rng = .Range("A1").CurrentRegion
End With
With Userform1.Combobox1
.Columncount = 3
.List = rng.Value
.BoundColumn = 1
End With
Activeworkbook.Close SaveChanges:=False
 
Top