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
