Listbox

[asukuo]

Hi all.
I have follow situation:
- Sheet Customers
A B C D E
1 Id Name Address City Phone
2 1 Rose 7, Red St. Paris 123456
3 2 Lisa 2, Blue Av. Milan 789012
4 3 Tom 5, Pink St. Rome 452134
5 4 Sam 3, Green St. London 327812
etc.

I have UserForm1 with following controls:
- Listbox1 ( alphabet letters)
- Listbox2 ( name)
- Textbox1 (Address)
- Textbox2 (City)
- Textbox3 (Phone)

and this code:

Option Explicit

Private Sub Userform_Activate()
Worksheets("Customers").Activate
End Sub

Private Sub UserForm_Initialize()
m_FillAlphabet ListBox1
End Sub

Private Sub m_FillAlphabet(MiaLista As MSForms.ListBox)
' Populate listbox1 with alphabet letters (Capital)
Dim intIndice As Integer, intalfa As Variant
Const CAPITAL = 65
intalfa = CAPITAL

MiaLista.Clear
For intIndice = 1 To 26
MiaLista.AddItem Chr(intalfa)
intalfa = intalfa + 1
Next
End Sub

Private Sub m_FillNames(MatchNome As String, MiaLista As MSForms.ListBox)
' Populate listbox2 with Names corresponding to the selected letter of
alphabet
Dim lngRiga As Long
Dim strLettera As String

strLettera = Left(MatchNome, 1)
MiaLista.Clear
lngRiga = 2
With Worksheets("Customers")
Do While .Cells(lngRiga, 1) <> ""
If StrComp(strLettera, Left(.Cells(lngRiga, 2), 1), _
vbTextCompare) = 0 Then
MiaLista.AddItem .Cells(lngRiga, 2)
End If
lngRiga = lngRiga + 1
Loop
End With
End Sub

Private Sub ListBox1_Change()
' populate Listbox2 with Names and clear Textboxes
m_FillNames ListBox1.List(ListBox1.ListIndex), ListBox2
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub

Private Sub ListBox2_Click() ' here need your help
TextBox1.ControlSource = ? code for Address
TextBox2.ControlSource = " for City
TextBox3.ControlSource = " for phone
End Sub

Thanks for any help.
Regards, John.

 

[asukuo]

on your form
one combo box name:=cmbAlphabet
3 text boxes:- txtAddress, txtCity, txtPhone
one listbox names lstNames. no need to set anything, this
is done in the code.
The code sets the listbox column count to two and hides
the second column. it also sets the bound column as 2.
Now the idea is that when a letter is selected, the tabel
from the sheet is 'read. and names that match the letter
get loaded to the listbox. This is column 1. we also load
the relevent row into column two. later, when a name is
slected, the row number , as this is the bound column
will be returned, and hence, we know where to go fot the
data.

Here's all the code for the form:-
Option Explicit
Private Sub UserForm_Initialize()
Dim i As Long
For i = 65 To 90
cmbAlphabet.AddItem Chr(i)
Next
With lstNames
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ";0"
End With
End Sub
Private Sub cmbAlphabet_Change()
Dim rw As Long, sName As String
lstNames.Clear
txtAddress = ""
txtCity = ""
txtPhone = ""
With Sheet1
rw = 2 ' firat row of data. row 1 is headers
Do Until .Cells(rw, 1) = ""
sName = .Cells(rw, 2).Value
If Left(sName, 1) = cmbAlphabet Then
lstNames.AddItem sName
lstNames.List(lstNames.ListCount - 1, 1)
= rw
End If
' point to the next row
rw = rw + 1
Loop
End With
End Sub
Private Sub lstNames_Click()
Dim rw As Long
rw = lstNames
txtAddress = Sheet1.Cells(rw, 3).Value
txtCity = Sheet1.Cells(rw, 4).Value
txtPhone = Sheet1.Cells(rw, 5).Value
End Sub

workbook is available - email me directly

Patrick Molloy
Microsoft Excel MVP

Thanks Patrick.
May i have copy of Workbook?
Regards, John.