Comboboxes on userforms

[deltasierra01]

I have a combobox on a userform, well call it "cbxHeadache1"

and a range well call "range1". Range 1 is a multicolumn range
existing on a worksheet of names, addresses, etc. Call it ColA, ColB,
ColC, etc.

The premise is that when you select a company name from the Combobox,
all of their address data populates to the various text boxes on the
userform.

What is the easiest way to program this in VBA, so that when
cbxHeadache change event fires, VBA pulls the data appropriate to the
row/selection for the combobox value, from the various columns to the
appropriate textbox on the userform?

TIA

 

[Dave Peterson]

Your combobox can support multiple columns--and all the columns don't have to be
visible. So you can pick up your array of values from the range (hiding columns
2 and 3) and then use those hidden values to populate the textboxes.

I put a commandbutton (cancel), 2 textboxes and a combobox on a userform.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ComboBox1_Change()
'clear out old
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""

With Me.ComboBox1
If .ListIndex < 0 Then
'nothing selected, do nothing
Else
Me.TextBox1.Value = .List(.ListIndex, 1)
Me.TextBox2.Value = .List(.ListIndex, 2)
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim LastRow As Long

With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("A1:C" & LastRow)
End With

With Me.ComboBox1
.ColumnCount = 3
.ColumnWidths = ";0;0"
.List = myRng.Value
End With
End Sub