Excel Combo Box

[xiphias]

I have a few named ranges in Excel, which I can quite happily use as a rowsource for a combo box. However, my external queries, despite the fact they define a range on import, can not be used. Any ideas?

[AtomicRex]

Here is some extracts from some example code to pull in a recordset and populate a combo box

Public Sub RefreshCPs()
Dim oConn As ADODB.Connection, oRsCp As ADODB.Recordset

m_bEvent = True
SheetProtection wsTrades, False
With wsTrades.Range("TradesDisp".Offset(1, 0)
.Resize(400).ClearContents
.Resize(400).Locked = True
End With
SheetProtection wsTrades, True
m_bEvent = False

wsTrades.cboCpNames.Clear: wsTrades.cboCpNames.Text = ""
wsTrades.cboContracts.Clear: wsTrades.cboContracts.Text = ""

Set oConn = ConnectToDatabaseITL
Set oRsCp = GetRsCounterPartiesDisp(oConn)

If Not (oRsCp.EOF) Then wsTrades.cboCpNames.List = TransposeArray(oRsCp.GetRows)

CloseObject oRsCp
CloseObject oConn
End Sub



Public Function ConnectToDatabaseITL() As Object
Dim sTargetDB As String, sConnStr As String
sTargetDB = ActiveWorkbook.Path & "\ITL.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sTargetDB & ";Jet OLEDBatabase Password=" & gmc_sITLDBPassword
Set ConnectToDatabaseITL = OpenConnection(sConnStr)
End Function


Public Function OpenConnection(sConnStr As String) As Object
Set OpenConnection = CreateObject("ADODB.Connection"
OpenConnection.Open sConnStr
End Function

Public Function OpenRecordset(oConn As Object, sSQL As String) As Object
Set OpenRecordset = CreateObject("ADODB.Recordset"
OpenRecordset.CursorLocation = gmc_ladUseClient
OpenRecordset.Open sSQL, oConn, gmc_ladOpenDynamic, gmc_ladLockOptimistic
End Function

Public Sub CloseObject(oObj As Object)
If Not (oObj Is Nothing) Then
If oObj.State = 1 Then oObj.Close
Set oObj = Nothing
End If
End Sub


Public Function TransposeArray(vArray As Variant) As Variant
Dim lRow As Long, lRows As Long, lCol As Long, lCols As Long, vTemp As Variant

lRows = UBound(vArray, 1): lCols = UBound(vArray, 2)
ReDim vTemp(lCols, lRows)
For lRow = 0 To lRows
For lCol = 0 To lCols
vTemp(lCol, lRow) = vArray(lRow, lCol)
Next lCol
Next lRow
TransposeArray = vTemp
End Function

[xiphias]

Ooo erk. I think I get the idea though..will try tomorrow.

[atomicrex]

Public Sub RefreshCPs()
cboCpNames.List = TransposeArray(oRsCp.GetRows)
End Sub

Basically this is the main bit you will need.

I pull in a recordset called oRsCp and use the GetRows to pull back all of the rows....however the getrows comes back inverted! Hence the TransposeArray function.

Anyway then you can set the details of the combobox by using the list property and set it equal to the array returned by the TransposeArray.

You can always use the same theory with setting an array equal to a range on a sheet.

So set up vArray=range("a1:c3"

Hope that helps a little more