Adding database data to a combo box...

[Lyndon]

Hi guys,


I have a working databse query thar returns a list of names. I want to

put this into a combo box. Currently I am inserting the database data
into a range on my spreadsheet (A1) and it uses as many rows as there
are names. In my combo box properties I can specify the ListFillRange
as A1:A5 or whatever and this works okay, but the thing is, as names
are added to the database, I want the range A1:A5 to update
dynamically.


Q1. Can I send the QueryTable data directly to the combo box
ListFillRange...


With ActiveSheet.QueryTables.Add(Co­nnection:=connstring,
Destination:=ComboBox1.ListFil­lRange etc.


Q2. If I can't do that, is there an Excel function that can tell me
the position of the last record in the range... A10 or A30 or whatever,

so I can use VB code to reset the range after the query returns...


.Refresh
End With


ComboBox1.ListFillRange("A1:" & something here...


Thanks guys.


Cheers,
Lyndon.

 

[Bob Phillips]

Create an Excel name (Insert>Name>Define) with a name of say myData and a
Refersto value of

=OFFSET($A$1,,,COUNTA($A:$A))

and use =myData in the combobox ListFillRange

--
HTH

Bob Phillips

Hi guys,


I have a working databse query thar returns a list of names. I want to

put this into a combo box. Currently I am inserting the database data
into a range on my spreadsheet (A1) and it uses as many rows as there
are names. In my combo box properties I can specify the ListFillRange
as A1:A5 or whatever and this works okay, but the thing is, as names
are added to the database, I want the range A1:A5 to update
dynamically.


Q1. Can I send the QueryTable data directly to the combo box
ListFillRange...


With ActiveSheet.QueryTables.Add(Co­nnection:=connstring,
Destination:=ComboBox1.ListFil­lRange etc.


Q2. If I can't do that, is there an Excel function that can tell me
the position of the last record in the range... A10 or A30 or whatever,

so I can use VB code to reset the range after the query returns...


.Refresh
End With


ComboBox1.ListFillRange("A1:" & something here...


Thanks guys.


Cheers,
Lyndon.