filling combo box
 

What is the best way to fill a combo box when the data is stored in an
external file?

filling combo box
 

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)

"Mike" wrote in message
news:g%mQb.7451$U%5.52032@attbi_s03...
What is the best way to fill a combo box when the data is stored in an
external file?

filling combo box
 

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


"Bob Phillips" wrote in message
...
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)

"Mike" wrote in message
news:g%mQb.7451$U%5.52032@attbi_s03...
What is the best way to fill a combo box when the data is stored in an
external file?

filling combo box
 

what kind of file is the data stored in?

--
Regards,
Tom Ogilvy


Mike wrote in message
news:jvyQb.109927$Rc4.780708@attbi_s54...
Is there a site with sample code? This is a combo box on the worksheet,
not
a userform.


"Bob Phillips" wrote in message
...
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)

"Mike" wrote in message
news:g%mQb.7451$U%5.52032@attbi_s03...
What is the best way to fill a combo box when the data is stored in an
external file?

filling combo box
 

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)

"Mike" wrote in message
news:jvyQb.109927$Rc4.780708@attbi_s54...
Is there a site with sample code? This is a combo box on the worksheet,
not
a userform.

filling combo box
 

excel 2000 and fixed width text file. text file contains over 1000 records


"Tom Ogilvy" wrote in message
...
what kind of file is the data stored in?

--
Regards,
Tom Ogilvy


Mike wrote in message
news:jvyQb.109927$Rc4.780708@attbi_s54...
Is there a site with sample code? This is a combo box on the worksheet,
not
a userform.


"Bob Phillips" wrote in message
...
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)

"Mike" wrote in message
news:g%mQb.7451$U%5.52032@attbi_s03...
What is the best way to fill a combo box when the data is stored in
an
external file?

filling combo box
 

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?


"Bob Phillips" wrote in message
...
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)

"Mike" wrote in message
news:jvyQb.109927$Rc4.780708@attbi_s54...
Is there a site with sample code? This is a combo box on the worksheet,
not
a userform.

filling combo box
 

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


--
Regards,
Tom Ogilvy


Mike wrote in message
news:JMTQb.19188$U%5.139067@attbi_s03...
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?


"Bob Phillips" wrote in message
...
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)

"Mike" wrote in message
news:jvyQb.109927$Rc4.780708@attbi_s54...
Is there a site with sample code? This is a combo box on the
worksheet,
not
a userform.