I dumped all 9 columns of the selected contact. But that doesn't mean you need
to. You could just use what you want.

If I want to display more than one column, I like to use a listbox. You can use
a combobox and the user will be able to see all the columns when they show the
list. But as soon as they choose an item, only one column will appear in the
combobox (as you've seen, I bet).

As a user, I like to see the info--and I think it's easier to see more info in a
listbox (without having to expand the dropdown (like in a combobox).

Anyway...

I created a small userform--two listboxes, a label and two commandbuttons.

I like to let the code do all the properties (except for the default stuff).
And I let the code determine the ranges--instead of using the dynamic name that
you defined.

That's a minor difference--you can change this later.

Here's the code that was behind the userform:

Option Explicit
Dim CustRng As Range
Dim ContRng As Range

Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim wks As Worksheet
Dim DestRow As Long
Dim cCtr As Long

Set wks = Worksheets("Sheet1")

If Me.ListBox2.ListIndex < 0 Then
'nothing chosen!
Else
Me.Label1.Caption = "" 'clear that label
End If

DestRow = 7 'however you determine what goes where

'all 9 columns!
For cCtr = 1 To ContRng.Columns.Count
With Me.ListBox2
wks.Cells(DestRow, cCtr).Value = .List(.ListIndex, cCtr - 1)
End With
Next cCtr

End Sub
Private Sub ListBox1_Change()

Dim myCell As Range
Dim cCtr As Long

'this shouldn't happen!
If Me.ListBox1.ListIndex < 1 Then
'nothing selected
Beep
Exit Sub
End If

Me.Label1.Caption = "Please select a contact"

Me.ListBox2.Clear

With Me.ListBox1
For Each myCell In ContRng.Columns(1).Cells
If LCase(myCell.Value) = LCase(.List(.ListIndex, 0)) Then
'it's a match
'add all 9 columns to
'add column D (firstname name)
With Me.ListBox2
.AddItem myCell.Value
'add next 8 columns
For cCtr = 2 To ContRng.Columns.Count
.List(.ListCount - 1, cCtr - 1) _
= myCell.Offset(0, cCtr - 1).Value
Next cCtr
End With
End If
Next myCell
End With

End Sub
Private Sub UserForm_Initialize()

With Worksheets("Customers")
'Set CustRng = .range("Customerrangetablehere") 'your name
'or let the code decide
Set CustRng = .Range("A2:B" & .Cells(.Rows.Count, "a").End(xlUp).Row)
End With

With Worksheets("Contacts")
Set ContRng = .Range("A2:i" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectSingle
.ColumnCount = CustRng.Columns.Count
.ColumnWidths = "30;30"
.ListStyle = fmListStyleOption
.RowSource = "" 'let the code do the work
'put the list in the listbox
.List = CustRng.Value 'all at once
End With

With Me.ListBox2
.MultiSelect = fmMultiSelectSingle
.ColumnCount = ContRng.Columns.Count
.ColumnWidths = "0;0;0;35;35;0;0;0;0"
.ListStyle = fmListStyleOption
.RowSource = ""
End With

With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
.Cancel = True
End With

With Me.CommandButton2
.Enabled = True
.Caption = "Process"
End With

Me.Label1.Caption = "Please select a company"

End Sub



dan dungan wrote:

Answers below inline:

So you choose one of the companyid/companyName choices from the first combobox.
Then you display another combobox/listbox of the contacts for that company,
right?

Right!

Each company is defined by the companyid field.

Yes.

Each companyid has at least 1 contact, but may have several contacts. Yes

After the user chooses the company, what gets populated?

Is it a listbox with all the possible contacts. . .

I was trying to use a combobox, but I don't really understand when to
use a combobox or when to use lisbox. All I need to show is the first
and last name so the user can choose the person that will eventually
get an email.

Or is it a worksheet (based on the activecell???) gets all the contact info for
each of the contacts that match the companyid?

The worksheet is a form. So I just realized I only need the CompanyId
and ContactID to be populated in the worksheet.

A2=Company Id
B2=ContactID

Then I import this and the quote data to Access and print the quote
from a report. Right now all I have is the company name. But I need to
add the contact so we can send the emails from access and so the quote
will have the contact's name, phone number and email address.

If you show a listbox, then what gets shown in that listbox--how many columns?

The user needs to see the company name and the contact first and last
name so they can choose the company and contact they are going to
quote.

Can that secondary listbox have multiple selected items--or just one?

Just one

Thanks again for your help.

Dan

--

Dave Peterson