Populate a dropdown in word from column in excel and then on dropdown change populate textboxes with related field (VBA)

Question

Hi,

I have the following code in a word 2007 macro where I populate a dropdown with customer names from an excel spreadsheet

Private Sub UserForm_Initialize()
Dim i As Integer
Dim cn As ADODB.Connection
Dim rsT As New ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=CCustomers.xls;Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With
rsT.Open "Select distinct * from Customer", cn, adOpenStatic

i = 0

With rsT
' This code populates the combo box with the values
' in the YourNamedRange named range in the .xls file. this exampletable is 2 rows by 6 columns and is set as a named range.

Do Until .EOF
ComboBox_Company.AddItem (i)
ComboBox_Company.Column(0, i) = rsT.Fields(0).Value
.MoveNext
i = i + 1
Loop
End With
End Sub

So I I have a column with customer names and I created a named range (Customer) and it populates the dropdown. However, when I select a customer in the dropdown I want to populate two address fields with (1 street, 2 city) the customer's address.

Private Sub cbo_customer_Change()
            Dim customerName As String
            customerName = cbo_customer.Value
End Sub

The spreadsheet has about 10 columns, Customer in the first one and address1 in the 9th and address2 in the last one. How can I use the variable customer to populate the address fields? Do I have to create a new named range with all the fields and have something like select customer, address1, address2 from myRange where customer = customerName?

Any help is appreciated.

Thanks in advance.

Answer 1

You answered the question yourself, create a named range with the columns your need and then do the select:

"SELECT customer, address1, address2 FROM NewRange WHERE customer = '" & customerName & "'"

Into a new record set, and get the address fields from item 0 in it.