Populate Drop-Down Listbox from Query?

I have a database I can access through ODBC. I'm familiar with how to
populate the spreadsheet from the database, but would like to find a
way to populate a drop-down list with information from a table in the
database, *without* dropping the list onto the spreadsheet (i.e., if
this were a validation box, we'd access the query info directly instead
of referencing a group of cells).

I imagine this can be done with some programming but its a weak point
of mine - can anyoen help?

O.Ben.D.

Do you know how to use Event Procedures? If not, search here and read up on
them.

The Query result table is a QueryTable object, which has an AfterRefresh
event. You can build an event procedure using AfterRefresh to populate the
combobox. The QueryTable.ResultRange gives you a way to access your
querytable (and so you can know the exact number of rows in it after the
refresh). Then the combo's .ListFillRange can be filled with whichever cells
you need.
--
- K Dales


"Oggie Ben Doggie" wrote:

> I have a database I can access through ODBC. I'm familiar with how to
> populate the spreadsheet from the database, but would like to find a
> way to populate a drop-down list with information from a table in the
> database, *without* dropping the list onto the spreadsheet (i.e., if
> this were a validation box, we'd access the query info directly instead
> of referencing a group of cells).
>
> I imagine this can be done with some programming but its a weak point
> of mine - can anyoen help?
>
> O.Ben.D.
>
>

Upon reread realized that you want to bypass the querytable alltogether?

This is a little more advanced. Use ADO. Need to add reference to
Microsoft ActiveX Data Objects to your project. You will also need to know
the command string to connect to your database; if you do not know it already
look at a querytable built off your database and in VBA look at its .Connect
property, which gives you the connection string.

The basic method to read the database table:

Sub GetMyData()
Dim MyDB as ADODB.Connection, MyRS as ADODB.Recordset
Dim SQLStr as String
Set MyDB = New ADODB.Connection
MyDB.Open(ConnectionString)
Set MyRS = New ADODB.RecordSet
With MyRS
.CursorLocation = adUseClient
' Modify the SQL String to retrieve the columns you want from your
table
' * in SQL means all columns; use SELECT Name, Code, .... FROM MyTable
' if you need to specify the columns
SQLStr = "SELECT * FROM MyTable"
.Open SQLStr, MyDB, adOpenDynamic, adLockReadOnly
' The above performed the query and stored the result in MyRS
' MyRS.RecordCount gives the number of records returned
If .RecordCount = 0 then
' No records; abort processing
Else
For i = 1 to RecordCount
ActiveSheet.ComboBox1.AddItem .Fields(0)
' Fields(0) above means column 1 - I think it is a
zero-based index?
' sorry - been a while and need to get going here!
Next i
End If
.. Close
End With

MyDB.Close
End Sub

Well, have to go but hope you got the idea!
--
- K Dales


"K Dales" wrote:

> Do you know how to use Event Procedures? If not, search here and read up on
> them.
>
> The Query result table is a QueryTable object, which has an AfterRefresh
> event. You can build an event procedure using AfterRefresh to populate the
> combobox. The QueryTable.ResultRange gives you a way to access your
> querytable (and so you can know the exact number of rows in it after the
> refresh). Then the combo's .ListFillRange can be filled with whichever cells
> you need.
> --
> - K Dales
>
>
> "Oggie Ben Doggie" wrote:
>
> > I have a database I can access through ODBC. I'm familiar with how to
> > populate the spreadsheet from the database, but would like to find a
> > way to populate a drop-down list with information from a table in the
> > database, *without* dropping the list onto the spreadsheet (i.e., if
> > this were a validation box, we'd access the query info directly instead
> > of referencing a group of cells).
> >
> > I imagine this can be done with some programming but its a weak point
> > of mine - can anyoen help?
> >
> > O.Ben.D.
> >
> >