Hello, below I am populating a combo box using ADO/SQL Server connection. Could someone show me an example of how to populate other cells on the Excel sheet based on the value selected from the combo box?
My code module:
Public Sub PopulateControl()
Dim cnRetailData As ADODB.Connection
Dim rsRetailData As ADODB.Recordset
Dim strCnn As String
' Open connection.
strCnn = "Provider=sqloledb; Data Source=TEST;Initial Catalog=TEST;" & _
"User Id=xx;Password=xx; "
Set cnRetailData = New ADODB.Connection
cnRetailData.Open strCnn
' Open Orders table.
Set rsOrders = New ADODB.Recordset
rsOrders.CursorType = adOpenKeyset
rsOrders.LockType = adLockOptimistic
rsOrders.Open "Orders", cnRetailData, , , adCmdTable
' Moves to the first record in the record set.
rsOrders.MoveFirst
' Loops through each entry in the record set and adds the last name
' for each entry into the combo box.
Do Until rsOrders.EOF
ActiveSheet.ComboBox1.AddItem rsOrders!order_name
' To use a ListBox control, use the following statement instead
' of the one above:
' UserForm1.ListBox1.AddItem rsOrders!order_name
'
' If the ComboBox or ListBox is on a worksheet instead of
' a UserForm, reference the worksheet instead of the UserForm:
' ActiveSheet.ComboBox1.AddItem rsOrders!order_name
rsOrders.MoveNext
Loop
' Displays the user form. You don't need this if you are not using
' a UserForm object.
'UserForm1.Show
' Closes the table.
rsOrders.Close
' Closes the connection.
cnRetailData.Close
End Sub
Thanks in advance,
CB
My code module:
Public Sub PopulateControl()
Dim cnRetailData As ADODB.Connection
Dim rsRetailData As ADODB.Recordset
Dim strCnn As String
' Open connection.
strCnn = "Provider=sqloledb; Data Source=TEST;Initial Catalog=TEST;" & _
"User Id=xx;Password=xx; "
Set cnRetailData = New ADODB.Connection
cnRetailData.Open strCnn
' Open Orders table.
Set rsOrders = New ADODB.Recordset
rsOrders.CursorType = adOpenKeyset
rsOrders.LockType = adLockOptimistic
rsOrders.Open "Orders", cnRetailData, , , adCmdTable
' Moves to the first record in the record set.
rsOrders.MoveFirst
' Loops through each entry in the record set and adds the last name
' for each entry into the combo box.
Do Until rsOrders.EOF
ActiveSheet.ComboBox1.AddItem rsOrders!order_name
' To use a ListBox control, use the following statement instead
' of the one above:
' UserForm1.ListBox1.AddItem rsOrders!order_name
'
' If the ComboBox or ListBox is on a worksheet instead of
' a UserForm, reference the worksheet instead of the UserForm:
' ActiveSheet.ComboBox1.AddItem rsOrders!order_name
rsOrders.MoveNext
Loop
' Displays the user form. You don't need this if you are not using
' a UserForm object.
'UserForm1.Show
' Closes the table.
rsOrders.Close
' Closes the connection.
cnRetailData.Close
End Sub
Thanks in advance,
CB